PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – extract nested value from json

I have a table with an id and the json column prod as the structure below

{
  "1": {
    "product": "chain",
    "spare_part": [],
    "sp_done": 0
  },
  "2": {
    "product": "bike",
    "spare_part": {
      "1": {"part": "pedal", ...},
      "2": {"part": "switch gear", ...},
      ...
    },
    "sp_done": 1
  }
}

How can I get a table like

id product spare_part sp_done
1 chain
2 bike pedal 1
2 bike switch gear 1

Thanks very much in advance.

Tags:

2

Answers


  1. 0

    You will have to write a function that loops over the nested JSON data and transforms it in a flat Array containing the data in the form that you need. If you want to write that function in JavaScript, you can use functions like Object.fromEntries, map, flatMap.

    Login or Signup to reply.
  2. 0

    Your data has spare_part once as an (empty) array, and once as an object. I assume you meant to use an array in both cases, and will present a solution for this. The basic idea is to pick elements from inner nodes of the JSON tree, expand children of these nodes and join them with these elements, then also pick elements of child elements.

    with recursive
input(json_content) as (
  values (
  '{
    "1": {
      "product": "chain",
      "spare_part": [],
      "sp_done": 0
    },
    "2": {
      "product": "bike",
      "spare_part": [
        {"part": "pedal"},
        {"part": "switch gear"}
      ],
      "sp_done": 1
    }
  }'::jsonb
))
select k::text                           id,
       v->>'product'                     product,
       coalesce(spare_part->>'part', '') spare_part,
       v->>'sp_done'                     sp_done
from       input
cross join jsonb_each(json_content) t(k,v)
left join  jsonb_array_elements(v->'spare_part') prod(spare_part) on (true)

    How does it work? The first CTE (input) is just a single JSON. The top-level object is then split with jsonb_each into rows, which contain the key k and value v. The query takes the id from k and the product and sp_done fields from p as parts of the
    result. It then splits the spare_parts field with jsonb_array_elements, and joins
    them. This joins each child row (array element) with its parent row. Then,
    coalesce(spare_part->>'part', '') takes the part field from each spare_part array
    element, using an empty string instead of null.

    The last join is a left join, to make sure that products get an entry even if their
    spare_parts array is empty. The JSON fields are extracted with ->>, which
    directly converts the JSON to text.

    The output is:

    id product spare_part sp_done
    1 chain 0
    2 bike pedal 1
    2 bike switch gear 1

    View on DB Fiddle.

    This can be generalized to include spare_part fields which are objects, too, by
    adding a second join, the first to process arrays, the second to process objects.
    But it would be tedious, so a uniform structure (all arrays) is preferable.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search