如何更深层次地查询PostgreSQL嵌套的JSONB？

Question

我是JSON/JSONB数据类型的新手，在选择更深层次的JSONB元素时遇到了一些问题。

CREATE TABLE person (id integer, details jsonb);

INSERT INTO person (id, details) VALUES
("id": 1, {"favorites":{"colors":"blue", "colors":"red"}}),
("id": 2),
("id": 3, {"favorites":{"colors":"blue", "colors":"red", "colors":"green"}});

------------------------ 
| id | Favorite colors |
------------------------ 
| 1  | blue            |
------------------------ 
| 1  | red             |
------------------------ 
| 3  | blue            |
------------------------ 
| 3  | red             |
------------------------ 
| 3  | green           |
------------------------

SELECT p.id, p.details ->'favorites' ->>'colors' AS "Favorite colors"
FROM "person" p;

SELECT p.id, json_array_elements((p.details ->'favorites' ->>'colors')::json) AS "Favorite colors"
FROM "person" p;

SELECT p.id, ((json_array_elements((json_array_elements(p.details::json)::json->>'favorites')::json))::json->>'colors') AS "Favorite colors"
    FROM "person" p;

对此有什么建议吗？

Answer 1

您提供的sql查询中存在一些语法错误。如果我正确地猜测了db模式，那么它们应该如下所示：

DROP TABLE person;
CREATE TABLE person (id integer, details jsonb);

INSERT INTO person (id, details) VALUES
(1, '{"favorites":[{"colors":"blue"}, {"colors":"red"}]}'::jsonb),
(2, '{}'::jsonb),
(3, '{"favorites":[{"colors":"blue"}, {"colors":"red"}, {"colors":"green"}]}'::jsonb);

如果这是正确的，您可以通过以下途径获得结果：

select id, jsonb_array_elements(details->'favorites')->'colors' from person