如何在Redshift中解析存储过程传入的动态JSON字符串？

实现方法 #

Redshift没有PostgreSQL中的jsonb_each_text函数，但可以通过组合现有JSON函数实现相同效果：

基础示例（直接解析JSON字符串）

WITH json_data AS (
    SELECT '{"name": "Bob", "age": "21", "section": "printing"}' AS json_str
),
json_keys AS (
    SELECT json_object_keys(json_parse(json_str)) AS key
    FROM json_data
)
SELECT 
    key,
    json_extract_path_text(json_parse(jd.json_str), key) AS value
FROM json_data jd
CROSS JOIN json_keys;

执行后返回结果：

key     value
---     ------
name    Bob
age     21
section printing

存储过程中使用（接收动态JSON参数）

如果要在存储过程中处理传入的动态JSON，可定义如下存储过程：

CREATE OR REPLACE PROCEDURE parse_dynamic_json(p_json_str VARCHAR)
AS $$
BEGIN
    WITH json_keys AS (
        SELECT json_object_keys(json_parse(p_json_str)) AS key
    )
    SELECT 
        key,
        json_extract_path_text(json_parse(p_json_str), key) AS value
    FROM json_keys;
END;
$$ LANGUAGE plpgsql;

关键函数说明 #

• json_parse：将字符串格式的JSON转换为Redshift可识别的JSON对象（需Redshift版本1.0.2177及以上）
• json_object_keys：提取JSON对象的所有顶层键，返回多行结果，每行一个键
• json_extract_path_text：根据指定键提取JSON对象中对应的值

内容的提问来源于stack exchange，提问作者dmurray