CODEWITHSUNDEEP
sqljsonoracle-databaseclob
Mohammed Az
Mohammed Az

Reputation: 19

How to use Oracle json_query correctly

I have a column called results with type CLOB in an oracle DB. it was an array like this one :

[
    {
      "id": 1,
      "vulnerabilities": [
        {
          "id": "vuln1",
          "severity": "High"
        },
        {
          "id": "vuln2",
          "severity": "Low"
        }
      ]
    },
    {
      "id": 2,
      "vulnerabilities": [
        {
          "id": "vuln3",
          "severity": "High"
        },
        {
          "id": "vuln4",
          "severity": "Low"
        }
      ]
    }
  ]

After some changes into our application the results was moved to another structure as :

{
  "version": 2,
  "results": [
    {
      "id": 1,
      "vulnerabilities": [
        {
          "id": "vuln1",
          "severity": "High"
        },
        {
          "id": "vuln2",
          "severity": "Low"
        }
      ]
    },
    {
      "id": 2,
      "vulnerabilities": [
        {
          "id": "vuln3",
          "severity": "High"
        },
        {
          "id": "vuln4",
          "severity": "Low"
        }
      ]
    }
  ]
}

I want to write a query to extract all the lines that have a vulnerability with severity High, I want to use only one json_exists and the logic will be in the json path, so I write

SELECT *
FROM my_json_table
WHERE json_exists(
  results,
  '$?((@.results[*].vulnerabilities[*]?(@.severity == "High")) || @.vulnerabilities[*](@.severity == "High"))'      
);

but somehow this is not working I can't use successive filters, please help

Upvotes: 0

Views: 78

Answers (1)

p3consulting
p3consulting

Reputation: 4640

Try this version:

json_exists(
  results, '$?(exists(@.vulnerabilities[*]?(@.severity == "High")) || exists(@.results[*].vulnerabilities[*]?(@.severity == "High")))'
)

Upvotes: 0

Related Questions