Filter Postgres Json array is subset of array using JsonPath

Question

I fail to find any information on how to do in / subsetof / contains queries using JsonPath in Postgres.

e.g. Assuming the following data in a jsonb column named data

{
   "name": "foo",
   "somearray" : [1,2,3,4,5]
}

Then I want to query this using something like

SELECT *
FROM mytable
where jsonb_path_exists(data, '($.somearray ??????? [2,4,6,8] ');

This does work:

SELECT *
FROM mytable
where jsonb_path_exists(data, '($ ? (@.somearray[*] == 2 || @.somearray[*] == 4 /*etc*/) ');

But I am hoping that there is some shorter syntax to do a proper subset test

I don't think there is any support for that. You would need to write your own code. Or normalize your data model to a state where you don't need to use JSON any more — user330315
– user330315, Commented Dec 11, 2021 at 13:35

Edouard · Accepted Answer · 2021-12-11 23:25:15Z

3

Unfortunately no jsonpath array operators, but you can still use the array operators :

SELECT *
FROM mytable
where (data->>'somearray') :: integer[] @> [2,4,6,8] ;

answered Dec 11, 2021 at 23:25

Edouard

7,1291 gold badge11 silver badges24 bronze badges

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

1 Comment

Many thanks, I find it somewhat odd that a Json query language does not support this. but I will go with your solution instead. 👍