0

As i've learned, if i need to flatten a JSON contained in a specific field of my postgres db table, i can use one of the following functions/operators

Now, those functions are perfect for a simple for some simple flattening like this one:

{
   "manufacturer":[
      {
         "manufacturer":"Tesla",
         "address":"Deer Creek Road Palo Alto",
         "contact":"[email protected]"
      },
      {
         "manufacturer":"BMW",
         "address":"Petuelring 130, 80809 München",
         "contact":"[email protected]"
      }
   ]
}

Using the jsonb_to_recordset function the json will be flattened without any problem.

Now, here comes my effective question, i'm not able to address in order to solve this problem effectively...

Suppose that every manufacturer has a nested JSON object that exposes the model and starting price like reported in the following JSON

{
   "cars":[
      {
         "manufacturer":"Tesla",
         "address":"Deer Creek Road Palo Alto",
         "contact":"[email protected]",
         "models":[
            {
               "model":"Model S",
               "starting_price":50000
            },
            {
               "model":"Model 3",
               "starting_price":35000
            },
            {
               "model":"Model X",
               "starting_price":70000
            }
         ]
      },
      {
         "manufacturer":"BMW",
         "address":"Petuelring 130, 80809 München",
         "contact":"[email protected]",
         "models":[
            {
               "model":"X1",
               "starting_price":35000
            },
            {
               "model":"X2",
               "starting_price":32000
            },
            {
               "model":"X3",
               "starting_price":39000
            },
            {
               "model":"Series 5",
               "starting_price":55000
            },
            {
               "model":"Series 3",
               "starting_price":50000
            }
         ]
      }
   ]
}

How i can create a query that also flattens the models object effectively and outputs many rows as the manufacturer models reported in the models object?

Nested JSON Flattening

Bonus Question:

In addition to that, it's possible to create a query that outputs something like this in a more compact way grouping rows as shown below?

Row Grouping

Any help is appreciated!

1 Answer 1

2

You need to unnest twice:

select x.m ->> 'manufacturer' as manufacturer,
       x.m ->> 'address' as address,
       x.m ->> 'contact' as contact,
       m.model ->> 'model' as model,
       (m.model ->> 'starting_price')::int as starting_price
from the_table t
  cross join jsonb_array_elements(t.models -> 'cars') as x(m)
  cross join jsonb_array_elements(x.m -> 'models') as m(model)

Suppressing repeating values is better done when you display the data in your application, not in SQL.

Online example

1
  • Is there any more compact way to organize this query?
    – VirtApp
    Sep 24, 2020 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.