How to query PostgreSQL nested JSONB in deeper level?

Question

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?

What "browser" are you referring to? Firefox? Chrome? And how do you display the result of the query in that browser? Which programming language are you using? And what exactly do you mean with "crash the browser"? Did you try to display less data in your browser? The memory consumption of the browser has nothing to do with the memory consumption of the Postgres server. What does explain (analyze, buffers) show you for those queries? — user330315
– user330315, Commented Sep 18, 2017 at 10:17

Dmitry Chirkin · Accepted Answer · 2017-09-18 14:57:37Z

3

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

answered Sep 18, 2017 at 14:57

Dmitry Chirkin

1,08410 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query PostgreSQL nested JSONB in deeper level?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related