如何在SAP HANA SQL中提取JSON字段键并转换为结构化表格？

现有数据表包含id列与存储JSON格式数据的statuses列，数据示例如下：

id| statuses                  |
--+---------------------------+
1 | { "a": true, "b": false } |
2 | { "c": true }             |

需要将其转换为如下结构的表格：

id | statusName | value
---+------------+------------------
1  | a          | true
1  | b          | false
2  | c          | true

尝试使用SAP HANA的JSON_TABLE函数时，发现该函数似乎仅适用于已知JSON键的场景，而当前无法预先知晓JSON中的键。此前尝试的SQL语句未能成功：

SELECT JT.*
FROM JSON_TABLE(<tableName>.statuses , '$')
COLUMNS
    (
        VALUE BOOLEAN PATH '$.*'
    )
) AS JT; --did not work

在SAP HANA中，可以通过JSON_TABLE结合键值对遍历的方式，动态提取JSON中的键和对应值，无需预先知道键名。以下是两种可行的SQL写法：

写法一：嵌套路径展开 #

SELECT 
    t.id,
    jt.statusName,
    jt.value
FROM 
    <tableName> t,
    JSON_TABLE(
        t.statuses,
        '$' COLUMNS (
            NESTED PATH '$.*' COLUMNS (
                statusName VARCHAR(255) KEY,
                value BOOLEAN VALUE
            )
        )
    ) AS jt;

写法二：使用keyvalue()路径 #

SELECT
    t.id,
    jt.key_name AS statusName,
    jt.val AS value
FROM
    <tableName> t,
    JSON_TABLE(
        t.statuses,
        '$.keyvalue()' COLUMNS (
            key_name VARCHAR(255) PATH '$.key',
            val BOOLEAN PATH '$.value'
        )
    ) AS jt;

说明 #

• $.keyvalue()是SAP HANA特有的路径表达式，能将JSON对象转换为包含key（键名）和value（对应值）的键值对数组。
• 如果JSON中的值类型不固定（比如同时存在布尔、字符串、数字等），可以将value的类型改为VARCHAR(255)或ANY来兼容多种类型。
• 替换语句中的<tableName>为你的实际表名即可执行。

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