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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

dbfiddle

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