Postgres JSONB - Flatten nested objects and groupings

Question

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?

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?

Any help is appreciated!

Answer 1

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