postgres json_populate_recordset not working as expected

Question

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?

Sorry everyone. The problem was in my data. Some of the records contained an object, some an array. This is why i was getting errors both ways. I think you for your time and apologize for the confusion. — Kopernik
– Kopernik, Commented Oct 22, 2016 at 3:57

klin · Accepted Answer · 2016-10-16 20:33:39Z

3

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.

edited Oct 16, 2016 at 20:33

answered Oct 16, 2016 at 19:49

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kopernik Over a year ago

Once I fixed my data to be consistent with my question, this answer worked as written. Thank you @klin.

Tim Biegeleisen · Accepted Answer · 2016-10-16 20:23:49Z

2

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

edited Oct 16, 2016 at 20:23

answered Oct 14, 2016 at 23:33

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

5 Comments

klin Over a year ago

(json_populate_record(null::c_history, payload)).* (not json_populate_recordset)

Kopernik Over a year ago

Pretty sure that complains about it needing to be an array of objects, but I'll try it.

Tim Biegeleisen Over a year ago

@avguchenko If it doesn't work, I'll go ahead and delete it.

Kopernik Over a year ago

@klin json_populate_record complains cannot call json_populate_record on an array

Kopernik Over a year ago

@TimBiegeleisen this statement complains cannot call json_populate_recordset on an object

Collectives™ on Stack Overflow

postgres json_populate_recordset not working as expected

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related