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

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