PostgreSQL: select from field with json format

Question

Table has column, named "config" with following content:

   {
     "A":{
        "B":[
              {"name":"someName","version":"someVersion"},
              {"name":"someName","version":"someVersion"}
            ]
         }
    }

The task is to select all name and version values. The output is expected selection with 2 columns: name and value.

I successfully select the content of B:

select config::json -> 'A' -> 'B' as B
from my_table;

But when I'm trying to do something like:

select config::json -> 'A' -> 'B' ->> 'name' as name,
       config::json -> 'A' -> 'B' ->> 'version' as version
from my_table;

I receive selection with empty-value columns

Why the ::json cast? If that column stores JSON data it should be defined as jsonb or at least json — user330315
– user330315, Commented Oct 26, 2021 at 7:40

Jim Jones · Accepted Answer · 2021-10-26 07:51:31Z

1

If the array size is fixed, you just need to tell which element of the array you want to retrieve,e.g.:

SELECT config->'A'->'B'->0->>'name' AS name,
       config->'A'->'B'->0->>'version' AS version
FROM my_table;

But as your array contains multiple elements, use the function jsonb_array_elements in a subquery or CTE and in the outer query parse the each element individually, e.g:

SELECT rec->>'name', rec->>'version'
FROM (SELECT jsonb_array_elements(config->'A'->'B') 
      FROM my_table) j (rec);

Demo: db<>fiddle

edited Oct 26, 2021 at 7:51

answered Oct 26, 2021 at 7:40

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

Comments

Edouard · Accepted Answer · 2021-10-26 08:02:05Z

1

First you should use the jsonb data type instead of json, see the documentation :

In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.

Using jsonb, you can do the following :

SELECT DISTINCT ON (c) c->'name' AS name, c->'version' AS version
FROM my_table
CROSS JOIN LATERAL jsonb_path_query(config :: jsonb, '$.** ? (exists(@.name))') AS c

answered Oct 26, 2021 at 8:02

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Comments

Pooya · Accepted Answer · 2021-10-26 08:38:10Z

0

dbfiddle

select e.value ->> 'name', e.value ->> 'version'
from 
  my_table cross join json_array_elements(config::json -> 'A' -> 'B') e

answered Oct 26, 2021 at 8:38

Pooya

3,2032 gold badges16 silver badges21 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: select from field with json format

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related