Sample Header Ad - 728x90

Multi dimensional JSON Array SQL Query

1 vote
1 answer
834 views
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
Asked by chris (11 rep)
Aug 26, 2022, 01:56 AM
Last activity: May 7, 2025, 10:05 PM