CODEWITHSUNDEEP
postgresqljsonbjsonpath
Stephane Desnault
Stephane Desnault

Reputation: 57

Querying an array of JSONB fields in PostgresQL

I have trouble figuring out how to query PostgreSQL JsonB datatype. I have a simple table with the structure:

CREATE TABLE myTable (id BIGINT PRIMARY KEY, answers JSONB)

All Json documents in the column "answers" are of the form:

[
{"R" : "aaa", "V" : 25},
{"R" : "aaa", "V" : 31}, 
{"R" : "bbb", "V" : 38}
...
]

There can be many elements in the list, but all elements will have a "R" and a "V" item.

I would like to retrieve a table using SQL, listing Ids, and a Json list of all the "V"s where "R" == "aaa" .

For the example above, I would get:

Any thoughts? Any help appreciated I have spent some time on the JSon paths examples available on the web, but haven't found something similar.

Thanks in advance.

Upvotes: 1

Views: 572

Answers (3)

Marth
Marth

Reputation: 24802

Note: Postgresql 12+ only

Using jsonpath:

WITH data(id, json_arr) AS (
    VALUES (1, $$[
      { "R": "aaa", "V": 25 },
      { "R": "aaa", "V": 31 },
      { "R": "bbb", "V": 38 }
    ]$$::JSONB)
)
SELECT id,
       -- $[*]             : "inside the top level array"
       -- ? (@.R == "aaa") : "keep only when the "R" key's value is "aaa""
       -- .V               : "select the "V" key of those elements"
       jsonb_path_query_array(json_arr, '$[*] ? (@.R == "aaa").V')
FROM data

returns:

+--+----------------------+
|id|jsonb_path_query_array|
+--+----------------------+
|1 |[25, 31]              |
+--+----------------------+

Note: you can also use

jsonb_path_query_array(
    json_arr,
    '$[*] ? (@.R == $r_value).V',
    '{"r_value": "aaa"}'  -- pass the 'r_value' to the query above
)

Upvotes: 3

Игорь Тыра
Игорь Тыра

Reputation: 955

all in pure JSONB:

SELECT id, jsonb_agg(ans->'V') FROM (
  SELECT id, jsonb_array_elements(answers) AS ans
  FROM myTable
) zz
WHERE ans->'R' = '"aaa"'
GROUP BY id;

Upvotes: 1

Mike Organek
Mike Organek

Reputation: 12484

I would expand the elements out, filter by R, and then reaggregate. This results in an int[] array.

select m.id, array_agg((a.obj->>'V')::int) as vvals
  from mytable m
 cross join lateral jsonb_array_elements(answers) as a(obj)
 where a.obj->>'R' = 'aaa';

Upvotes: 1

Related Questions