Querying an array of JSONB fields in PostgresQL

Question

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:

• Id for the record
• [25, 31] -- the two "V" values where "R" == "aaa"

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.

Answer 1

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
)