JSON对象上的连接表

Question

我有表A，它有JSON列f，其内容如下：

[{"name": "abc", "id": "1"}, {"name": "abcd", "id": "2"}, {"name": "abcde", "id": "3"} ]

我想在上面描述的JSON对象中加入另一个id表B，但也从JSON对象获取属性name。

我设法创建了以下查询：

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

返回以下结果：

id, name
1, "abc"
2, "abcd"
3, "abcde"

表B中的抽样数据：

id, title, slug  
1, "title 1", "title-1"
2, "title 2", "title-2"
3, "title 3", "title-3"

如何将此结果与表B连接，并从该表中添加更多的数据(列)？

预期结果：

id, name, title, slug
1, "abc", "title 1", "title-1"
2, "abcd", "title 2", "title-2"
3, "abcde", "title 3", "title-3"

Answer 1

Click: demo:db<>fiddle

SELECT
    b.id,
    elems ->> 'name' as name,           -- 3
    b.title
FROM
    a,
    json_array_elements(f) as elems     -- 1
JOIN
    b ON b.id = (elems ->> 'id')::int   -- 2

使用json_array_elements()

• Now从json数组中获取元素，您可以在JSON属性(在本例中为text：id作为text并将其转换为int值)上加入

• ，然后从b和数组元素(如name)

)获取所有相关值