如何不使用正则获取json_extract提取的嵌套JSON首个键名

问题场景 #

给定JSON数据：

{"GrossORNet":"Net","Term":"Monthly","Tier":{"All":{"1":{"Max":"100000","Min":"1","Rate":"15","StartDate":"2021-02-01","EndDate":"2021-02-01"}}}}

用SELECT json_extract('上述JSON内容', '$.Tier');能拿到{"All":{"1":...}}结构的结果，现在要提取这个对象的第一个键名「All」，不用正则表达式的话可以这么做：

1. SQLite 实现 #

从SQLite 3.38.0版本起，支持json_each()和json_object_keys()函数，两种方式都能拿到首个键：

• 方式一：遍历JSON对象取首个键

SELECT key
FROM json_each(json_extract('{"GrossORNet":"Net","Term":"Monthly","Tier":{"All":{"1":{"Max":"100000","Min":"1","Rate":"15","StartDate":"2021-02-01","EndDate":"2021-02-01"}}}}', '$.Tier'))
LIMIT 1;

• 方式二：先拿所有键再取第一个元素

SELECT json_object_keys(json_extract('{"GrossORNet":"Net","Term":"Monthly","Tier":{"All":{"1":{"Max":"100000","Min":"1","Rate":"15","StartDate":"2021-02-01","EndDate":"2021-02-01"}}}}', '$.Tier'))[0] AS first_key;

2. MySQL 实现 #

MySQL用JSON_KEYS()获取所有键后，提取第一个元素即可：

SELECT JSON_EXTRACT(JSON_KEYS(JSON_EXTRACT('{"GrossORNet":"Net","Term":"Monthly","Tier":{"All":{"1":{"Max":"100000","Min":"1","Rate":"15","StartDate":"2021-02-01","EndDate":"2021-02-01"}}}}', '$.Tier')), '$[0]') AS first_key;

也可以用JSON_TABLE()更灵活地解析：

SELECT j.key
FROM JSON_TABLE(
    JSON_EXTRACT('{"GrossORNet":"Net","Term":"Monthly","Tier":{"All":{"1":{"Max":"100000","Min":"1","Rate":"15","StartDate":"2021-02-01","EndDate":"2021-02-01"}}}}', '$.Tier'),
    '$.*' COLUMNS(
        key VARCHAR(255) PATH '$."$key"'
    )
) AS j
LIMIT 1;

3. PostgreSQL 实现 #

PostgreSQL用jsonb_object_keys()提取所有键，再通过LIMIT 1取第一个：

SELECT jsonb_object_keys('{"GrossORNet":"Net","Term":"Monthly","Tier":{"All":{"1":{"Max":"100000","Min":"1","Rate":"15","StartDate":"2021-02-01","EndDate":"2021-02-01"}}}}'::jsonb -> 'Tier')
LIMIT 1;

或者用json_each()遍历取键：

SELECT (json_each('{"GrossORNet":"Net","Term":"Monthly","Tier":{"All":{"1":{"Max":"100000","Min":"1","Rate":"15","StartDate":"2021-02-01","EndDate":"2021-02-01"}}}}'::json -> 'Tier')).key
LIMIT 1;

内容的提问来源于stack exchange，提问作者Md. Parvez Alam