我有一个表A,该表的JSON列Key
具有以下内容:
equals
我想在上述JSON对象内的f
上连接另一个表B,但还要从JSON对象中获取属性[{"name": "abc","id": "1"},{"name": "abcd","id": "2"},{"name": "abcde","id": "3"} ]
。
我设法创建了以下查询:
id
返回以下结果:
name
表B中的样本数据:
WITH sample_data_array(arr) AS (
SELECT f FROM A
),sample_data_elements(elem) AS (
SELECT json_array_elements(arr) FROM sample_data_array
)
SELECT CAST(elem->>'id' AS int) AS id,elem->'name' AS name FROM sample_data_elements
如何将此结果与表B合并,并从表中添加更多数据(列)?
预期结果:
id,name
1,"abc"
2,"abcd"
3,"abcde"