How to break out jsonb array into rows for a postgresql query

Question

I have the objective of breaking out the results of a query on a table with a json column that contains an array into individual rows. However I'm not sure about the syntax to write this query. I'm using this:

For the following query

SELECT
       jobs.id,
       templates.Id,
       templates.Version,
       templates.StepGroupId,
       templates.PublicVersion,
       templates.PlannedDataSheetIds,
       templates.SnapshottedDataSheetValues
FROM jobs,
     jsonb_to_recordset(jobs.source_templates) AS templates(Id, Version, StepGroupId, PublicVersion,
                                                            PlannedDataSheetIds, SnapshottedDataSheetValues)

On the following table:

create table jobs
(
    id uuid default uuid_generate_v4() not null
        constraint jobs_pkey
            primary key,
    source_templates jsonb,
);

with the jsonb column containing data in this format:

[  
   {  
      "Id":"94729e08-7d5c-459d-9244-f66e17059fc4",
      "Version":1,
      "StepGroupId":"0274590b-c08d-4963-b37e-8fc8f25151d2",
      "PublicVersion":1,
      "PlannedDataSheetIds":null,
      "SnapshottedDataSheetValues":null
   },
   {  
      "Id":"66791bfd-8cdb-43f7-92e6-bfb45b0f780f",
      "Version":4,
      "StepGroupId":"126404c5-ed1e-4796-80b1-ca68ad486682",
      "PublicVersion":1,
      "PlannedDataSheetIds":null,
      "SnapshottedDataSheetValues":null
   },
   {  
      "Id":"e3b31b98-8052-40dd-9405-c316b9c62942",
      "Version":4,
      "StepGroupId":"bc6a9dd3-d527-449e-bb36-39f03eaf87b9",
      "PublicVersion":1,
      "PlannedDataSheetIds":null,
      "SnapshottedDataSheetValues":null
   }
]

I get an error:

[42601] ERROR: a column definition list is required for functions returning "record"

What is the right way to do this without generating the error?

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2019-07-16T19:52:11

You need to define datatypes:

SELECT
       jobs.id,
       templates.Id,
       templates.Version,
       templates.StepGroupId,
       templates.PublicVersion,
       templates.PlannedDataSheetIds,
       templates.SnapshottedDataSheetValues
FROM jobs,
     jsonb_to_recordset(jobs.source_templates) 
         AS templates(Id UUID, Version INT, StepGroupId UUID, PublicVersion INT,
                      PlannedDataSheetIds INT, SnapshottedDataSheetValues INT)

How to break out jsonb array into rows for a postgresql query

1 Answers