Expanding an array of objects stored in JSONB

Question

I've got a table that includes a JSONB field with a JSON array with a simple format that I'd like to expand. Here's a slimmed version of the table's definition:

CREATE TABLE IF NOT EXISTS data.data_file_info (
    id uuid NOT NULL,
    server_name_ citext NOT NULL,
    table_stats jsonb DEFAULT '{}'::jsonb NOT NULL,
    PRIMARY KEY(id)
);

Here's a bit of sample data from the table_stats JSONB field:

[
    {"table_name":"AutoReportOrg","record_count":48,"table_number":167},
    {"table_name":"AutoReportOrgAudit","record_count":0,"table_number":170},
    {"table_name":"AutoReportOrgRecipient","record_count":126,"table_number":168},
    {"table_name":"AutoReportRecipient","record_count":28,"table_number":169}
]

The json_populate_recordset and/or json_to_recordset functions seem like they should be what I'm after, but I haven't been able to get them working. I've looked at a lot of past questions, and tried out various bits of syntax. Below is what I get the impression should work...but it isn't:

-- Define a custom type to act as a template for the JSON parser.
DROP TYPE IF EXISTS dfits;
CREATE TYPE api.dfits AS (
    table_name citext,
    record_count int8,
    table_number int4);

-- And now we parse!
SELECT server_name_, (json_populate_recordset(null::dfits, table_stats)).* FROM data_file_info;

But I get this back:

ERROR:  function json_populate_recordset(dfits, jsonb) does not exist
LINE 4: SELECT server_name_, (json_populate_recordset(null::dfits, t...
                              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts. (Line 9)

Can anyone spot the error in my setup? It seems like I've got the right pieces, but I'm not managing to put them together properly.

Postgres 11.4, deployed on RDS.

Follow Up

GMB answered my question, but I want to add a bit more here for the archives. Past my typing error (JSON instead of JSONB), the crux of this stuff seems to be that Postgres needs a template/map that defines the structure of the elements in the JSON/JSONB array. I think that there are four (?) places that typing data can come from in this case:

• An existing table definition, as each table is a type.
• An existing view definition, as each view is a type. (You can find them in pg_class along with base table-derived types.
• A custom type created with CREATE TYPE, like I used above.
• An in-line declaration.

For comparison, here's an updated query that uses the custom type dfits shown above:

SELECT server_name_, expanded_json.*
FROM
   data_file_info, 
   jsonb_populate_recordset(null::dfits, table_stats) as expanded_json

And now here's another version that uses an in-line declaration:

SELECT server_name_, expanded_json.*
FROM
   data_file_info, 
    jsonb_to_recordset(table_stats) as expanded_json 
        (table_name text, record_count int, table_number int)

I can see uses for either approach. But, with that said, CREATE TYPE is pretty great. And you can combine it with CREATE CAST to make for very compact and easy-to-use code.

Answer 1

Since your are using a jsonb column, you actually need jsonb_populate_recordset. Also, this function returns a set of records, so it needs to be placed in the FROM clause, not in the SELECT clause.

Consider:

SELECT server_name, x.*
FROM
   data_file_info, 
   jsonb_populate_recordset(null::dfits, table_stats) x

Demo on DB Fiddle:

| server_name | table_name             | record_count | table_number |
| ----------- | ---------------------- | ------------ | ------------ |
| foo         | AutoReportOrg          | 48           | 167          |
| foo         | AutoReportOrgAudit     | 0            | 170          |
| foo         | AutoReportOrgRecipient | 126          | 168          |
| foo         | AutoReportRecipient    | 28           | 169          |