Filter Postgres Json array is subset of array using JsonPath-CodePudding

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

CodePudding user response：

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

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