CODEWITHSUNDEEP
postgresqlpostgresql-9.4jsonb
ktalez
ktalez

Reputation: 61

How to query PostgreSQL nested JSONB in deeper level?

I'm new to JSON/JSONB datatypes and I'm having some problems selecting the JSONB elements from deeper levels.

Here is an example table:

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"}});


I would like to select all p.details ->'favorites' ->>'colors', for example:

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


The following (or similar) gives empty column for Favorite colors (tried also with jsonb_array_elements):

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;


The following works with a simple query, but it crashes the browser when retrieving more data (the end users use the queries in a web browser). 

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

Seems that the browser is consuming lot of memory. I've found some posts saying that Multiple calls to json_array_elements slow down the query: https://www.postgresql.org/message-id/[email protected]

Any advice on this?

Upvotes: 6

Views: 4458

Answers (1)

Dmitry Chirkin
Dmitry Chirkin

Reputation: 1084

There is some syntax errors in sql queries you provided. If I correctly guessed db schema than they should looks like this: 

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);

If this is correct than you can get the results via: 

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

Upvotes: 3

Related Questions