Oracle JSON query

Question

I have a JSON column which on persons table which stores the best activities people have done in countries they visited like this...

{
  "countries": [
    {
      "name": "Ireland",
      "bestActivity": {
        "name": "Drinking"
      }
    },
    {
      "name": "Scotland",
      "bestActivity": {
        "name": "Dancing"
      }
    }
  ]
}

Someone else might be:

{
  "countries": [
    {
      "name": "Ireland",
      "bestActivity": {
        "name": "Football"
      }
        },
    {
      "name": "England",
      "bestActivity": {
        "name": "Golf"
      }
        }
  ]
}

I want to select all the people who visited Ireland and with best activity of Drinking in Ireland and also visited Scotland and with the best activity of Drinking.

Really struggling. Any tips?

Answer 1

You can use JSON_TABLE() function as your database version is 12c(for all subversions) such as

SELECT first_name
  FROM t,   
  JSON_TABLE(jscol, '$.countries[*]' 
       COLUMNS ( 
                country      VARCHAR2(90) PATH '$.name',  
                bestActivity VARCHAR2(90) PATH '$.bestActivity.name'
               )
            )
 WHERE (country = 'Ireland' AND bestActivity = 'Drinking')
    OR  country = 'Scotland'
 GROUP BY first_name
 HAVING COUNT(*)>1

Demo

You can use JSON_EXISTS() function is database version is 12cR2+ in such a format

SELECT first_name
  FROM t
 WHERE JSON_EXISTS(jscol,
                    '$.countries?(@.name == "Ireland"
                               && @.bestActivity.name == "Drinking")')
   AND JSON_EXISTS(jscol,                            
                    '$.countries?(@.name == "Scotland")');

Demo

Notice that the query should be invoked after issuing SET DEFINE OFF when SQL*Plus is used in order to prevent the && operands to be interpreted as a substitution variable.

Answer 2

You can use the JSON_EXISTS function to filter your table based on the criteria you described. I have built a sample query below using the JSON structures you provided in your question.

WITH
    sample_table (first_name, json_col)
    AS
        (SELECT 'John', '{
     "countries": [
        { 
            "name": "Ireland",
            "bestActivity" : {
                 "name": "Drinking"
             }, 
        },
        { 
            "name": "Scotland",
            "bestActivity" : {
                  "name": "Dancing"
             }
        }
 }' FROM DUAL
         UNION ALL
         SELECT 'Jane', '{
     "countries": [
        { 
            "name": "Ireland",
            "bestActivity" : {
                 "name": "Football"
             }, 
        },
        { 
            "name": "England",
            "bestActivity" : {
                  "name": "Golf"
             }
        }
 }' FROM DUAL)
SELECT *
  FROM sample_table s
 WHERE     JSON_EXISTS (s.json_col, '$.countries[*]?(@.name == "Scotland")')
       AND JSON_EXISTS (
               s.json_col,
               '$.countries[*]?(@.name == "Ireland" && @.bestActivity.name == "Drinking")');