CODEWITHSUNDEEP
postgresqljsonb
Morris de Oryx
Morris de Oryx

Reputation: 2183

Passing a row type to jsonb_to_recordset syntax errors

I'm trying to work out how to expand a JSONB field with a jsonb_to_recordset and a custom type.

Postgres 11.4.

This is just a test case, but for the minute I'm trying to work out the syntax. I've got a table named data_file_info with a JSONB field named table_stats. The JSONB field always includes a JSON array with the same structure:

[
    {"table_name":"Activity","record_count":0,"table_number":214},
    {"table_name":"Assembly","record_count":1,"table_number":15},
    {"table_name":"AssemblyProds","record_count":0,"table_number":154}
]

The following code works correctly:

  from data_file_info,
       jsonb_to_recordset(table_stats) as table_stats_splat (
            table_name text, 
            record_count integer,
            table_number integer
        )

What I would like to do is pass in a custm type definition instead of the long-form column definition list shown above. Here's the matching type:

create type data.table_stats_type as (
    table_name text, 
    record_count integer,
    table_number integer)

Some examples I've seen, and the docs say, that you can supply a type name using a null:row_type casting in the first parameter to jsonb_to_recordset. The examples that I've found use in-line JSON, while I'm trying to pull stored JSON. I've made a few attempts, all have failed. Below are two of the trials, with errors. Can someone point me towards the correct syntax?

FAIL:

select table_stats_splat.*

   from data_file_info,
        jsonb_populate_recordset(null::table_stats_type, data_file_info) as table_stats_splat;

-- ERROR:  function jsonb_populate_recordset(table_stats_type, data_file_info) does not exist

-- LINE 4:         jsonb_populate_recordset(null::table_stats_type, dat...
            ^

-- HINT:  No function matches the given name and argument types. You might need to add explicit type casts. (Line 4)

FAIL:

select *
  from jsonb_populate_recordset(NULL::table_stats_type, (select table_stats from data_file_info)) as table_stats_splat;

 -- ERROR:  more than one row returned by a subquery used as an expression. (Line 2)

I'm doubtlessly missing something pretty obvious, and am hoping someone can suggest what that is.

Upvotes: 2

Views: 3134

Answers (1)

klin
klin

Reputation: 121644

Use the column as the second parameter:

select table_stats_splat.*
from data_file_info,
    jsonb_populate_recordset(null::table_stats_type, table_stats) as table_stats_splat;

Upvotes: 4

Related Questions