Select JSON from multiple rows by keys Postgress-CodePudding

I need to return only the JSON from multiple rows where keys are matched. What am I missing?

SELECT jsonb_object_agg(key, value) FROM jsonb_each((SELECT data from hit_count_13306823936951630992)) WHERE key IN ('8517', '1099');
ERROR:  more than one row returned by a subquery used as an expression

CodePudding user response：

You need to call jsonb_each once for each row, not on the rows collectively. You can do this with a lateral join.

SELECT jsonb_object_agg(key, value) FROM hit_count_13306823936951630992 
   CROSS JOIN LATERAL jsonb_each(data) WHERE key IN ('8517', '1099');

The LATERAL keyword is optional, since set-returning functions are always lateral.

CodePudding user response：

The following finds each instance of a given key per row creates a new object and aggregates the objects into a single object. While I consider this sub optimal. It fulfills the purpose.

SELECT jsonb_object_agg(data_timestamp, jsonb_build_object('8517',data->'8517','1099',data->'1099')) FROM hit_count_13306823936951630992;