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.

Do you want a jsonb, json, or int[] array?

Bergi
– Bergi

2020-08-24 18:42:49 +00:00
Commented Aug 24, 2020 at 18:42 — Bergi
– Bergi, Commented Aug 24, 2020 at 18:42
Jsonb/Json would be best

Stephane Desnault
– Stephane Desnault

2020-08-24 20:48:43 +00:00
Commented Aug 24, 2020 at 20:48 — Stephane Desnault
– Stephane Desnault, Commented Aug 24, 2020 at 20:48

Marth · Accepted Answer · 2020-08-24 18:20:15Z

3

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
)

edited Aug 24, 2020 at 18:20

answered Aug 24, 2020 at 17:59

Marth

24.9k3 gold badges68 silver badges76 bronze badges

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

2 Comments

Игорь Тыра Over a year ago

only for postgresql-12

Marth Over a year ago

@ИгорьТыра: True, though I assumed this was OK since the OP mentioned jsonpath in his question. I'll edit my answer.

Mike Organek · Accepted Answer · 2020-08-24 17:42:40Z

1

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';

answered Aug 24, 2020 at 17:42

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

1 Comment

Laurenz Albe Over a year ago

The result should probably be a JSON array.

Игорь Тыра · Accepted Answer · 2020-08-24 17:54:57Z

1

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;

answered Aug 24, 2020 at 17:54

Игорь Тыра

9755 silver badges12 bronze badges

1 Comment

Stephane Desnault Over a year ago

Thanks, great answer, and very readable too. Marth's answer is a bit faster, with the constraint of PG12, so I picked it, but yours was very helpful too in pushing my understanding forward!

Collectives™ on Stack Overflow

Querying an array of JSONB fields in PostgresQL

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related