Multi dimensional JSON Array SQL Query

Question

I'm struggling to write an appropriate query for my data ->

 "schools":[
 {
 "org_symbol":"School 1",
 "criteria":[
 [
 {
 "value":"private",
 "type":"school type"
 },
 {
 "value":"usa",
 "type":"country"
 },
 {
 "value":"english",
 "type":"language"
 },
 {
 "value":"1-6",
 "type":"grades"
 },
 {
 "value":"Silver",
 "type":"level"
 }
 ]
 ]
 },
 {
 "org_symbol":"School 2",
 "criteria":[
 [
 {
 "value":"private",
 "type":"school type"
 },
 {
 "value":"usa",
 "type":"country"
 },
 {
 "value":"english",
 "type":"language"
 },
 {
 "value":"1-6",
 "type":"grades"
 },
 {
 "value":"gold",
 "type":"level"
 }
 ]
 ]
 }
 ]
}

I have this

SELECT distinct on(id) * FROM tribes, json_array_elements(meta::json -> 'attributes') as elem 
WHERE 
( 
 (elem ->> 'type' = 'school type' and elem ->> 'value' = 'private') 
 and (elem ->> 'type' = 'country' and elem ->> 'value' = 'usa') 
 and (elem ->> 'type' = 'language' and elem ->> 'value' = 'english')
 and (elem ->> 'type' = 'grades' and elem ->> 'value' = '1-6')
 and (elem ->> 'type' = 'level' and elem ->> 'value' = 'gold')
 ) ;

but it doesn't return anything, I know i'm indexing correctly (full json not included) but I can't seem to get the multi query to work. I need to be able to check where value and type match each set of criteria

I think i'm close but really not sure, any help would be greatly appreciated

• user1822: I don't see any key name attributes in your sample data. So obviously -> 'attributes' returns nothing

Answer 1

If you want to check if there is at least one school entry in the schools array that contains all those key/value pairs, you can use the @> operator:

select *
from tribes
where meta -> 'schools' @> '[{"criteria": [[ {"value": "usa", "type": "country"}, 
 {"value": "private", "type": "school type"},
 {"value": "english", "type": "language"},
 {"value": "1-6", "type": "grades"},
 {"value": "gold", "type": "level"}
 ]]
 }]';

This assumes that meta is a jsonb column (which it really should be). If it's not you'll have to cast it: meta::jsonb

Online example