CODEWITHSUNDEEP
jsonpostgresql
Kopernik
Kopernik

Reputation: 2823

postgres json_populate_recordset not working as expected

I have a table called slices with some simple json objects that looks like this:

id | payload                               | metric_name
---|---------------------------------------|------------
1  | {"a_percent":99.97,"c_percent":99.97} | metric_c
2  | {"a_percent":98.37,"c_percent":97.93} | metric_c

many records of this. I am trying to get this:

a_percent | c_percent
----------|----------
99.97     | 99.97
98.37     | 97.93

I am creating the type and using json_populate_recordset along with json_agg in the following fashion:

CREATE TYPE c_history AS(
    "a_percent" NUMERIC(5, 2),
    "c_percent" NUMERIC(5, 2)
);

SELECT * FROM
    json_populate_recordset(
        NULL :: c_history,
        (
            SELECT json_agg(payload::json) FROM slices
            WHERE metric_name = 'metric_c'
        )
    );

The clause select json_agg(...) by itself produces a nice array of json objects, as expected:

[{"a_percent":99.97,"c_percent":99.97}, {"a_percent":98.37,"c_percent":97.93}]

But when I run it inside json_populate_recordset, I get Error : ERROR: must call json_populate_recordset on an array of objects.

What am I doing wrong?

Upvotes: 2

Views: 4906

Answers (2)

klin
klin

Reputation: 121644

This is a variant of @TimBiegeleisen's solution with the function json_populate_record() used in a from clause:

select id, r.* 
from slices, 
lateral json_populate_record(null::c_history, payload) r;

See rextester or SqlFiddle.

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521534

You don't need to use json_agg, since it appears you want to get the set of a_percent and c_percent values for each id in a separate record. Rather just call json_populate_recordset as follows:

SELECT id, (json_populate_record(null::c_history, payload)).* FROM slices

Upvotes: 2

Related Questions