sqlpostgresqlquery-optimizationjsonb

How to extract nested JSONB values in PostgreSQL with dynamic SQL query for a particular key?


I have a JSONB column called metadata in my PostgreSQL database. I want to extract the values of a specific key within this column.

For example, consider the following JSON structure:

{
  "key1": "value1",
  "key2": [
    {"key3": "value3"},
    {"key3": "value4"}
  ]
}

I need to retrieve the values of key3, even if they are nested at various levels (up to 4 or 5 levels deep). Is there a generic approach to dynamically generate a PostgreSQL SQL query for this purpose?


Solution

  • The jsonb_path_query() function can do that: demo at db<>fiddle

    create table your_table(
       id int generated by default as identity primary key
      ,metadata  jsonb);
    insert into your_table(metadata) values
     ('{"key1": "value1",
        "key2": [ {"key3": "value3"}
                 ,{"key3": "value4"} ]
       }')
    ,('{"key3": "value5",
        "key2": [ {"key4": {"key3": "value6"}} ]
       }');
    
    SELECT jsonb_path_query(metadata,'strict $.**.key3')
    FROM your_table
    WHERE metadata @? '$.**.key3';
    
    jsonb_path_query
    "value3"
    "value4"
    "value5"
    "value6"