Question 114727 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Select JSON from multiple rows by keys Postgress

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search