1

In a bit of a pickle at work - I have a Jsonb which I need to flatten a file that has similar structure:

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

I want this in a table format where manufacturing are column names! Something like this - Postgres JSONB - Flatten nested objects and groupings! Can't seem to understand how to get there. He has mentioned using jsonb_to_recordset but not sure how to get everything in a typical csv format.

Example of how I want to see the data -

manufacturer |

5
  • How do you want to show this? name is an array so in theory could have multiple objects, do you want that as a separate table, or to somehow correlate back to manufacturer in a single table? Commented Jul 24, 2022 at 3:58
  • manufacturer is an array as well. How do the array elements of manufacturer and name relate? What is the output you want from that example?
    – user1822
    Commented Jul 24, 2022 at 9:20
  • @Charlieface I'm trying to convert let's say Manufacturer into a CSV - ie even if I have 100 different manufacturers ie TESLA,BMW,AUDI with their information I see them in a CSV format with columns such as Manufacturer, address, contact etc I get it initially my question is not possible. Would appreciate your help!
    – NasahT
    Commented Jul 25, 2022 at 21:23
  • Please show in your question how you would like to see the information Commented Jul 25, 2022 at 21:55
  • Made the edits! Let me know if it makes sense. The code you sent is not working! ERROR: column t.jsoncolumn does not exist LINE 3: CROSS JOIN jsonb_to_recordset(t.jsonColumn->'Manufacturers')
    – NasahT
    Commented Jul 25, 2022 at 22:17

1 Answer 1

1

You can indeed use jsonb_to_recordset, but you also need to use -> to pull out the manufacturer property.

Your expected result is unclear, but it looks like you could do this:

SELECT j.*
FROM YourTable t
CROSS JOIN jsonb_to_recordset(t.jsonColumn->'manufacturer')
    AS j(manufacturer varchar(100), address varchar(100), contact varchar(100));

db<>fiddle

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.