CODEWITHSUNDEEP
sqljsonpresto
KOB
KOB

Reputation: 4545

Parse JSON values with arbitrary keys

I have a column in a table that is a JSON string. Part of these strings have the following format:

{
    ...
    "rules": {
        "rule_1": {
            "results": [],
            "isTestMode": true
        },
        "rule_2": {
            "results": [],
            "isTestMode": true
        },
        "rule_3": {
            "results": [
                {
                    "required": true,
                    "amount": 99.31
                }
            ],
            "isTestMode": true
        },
        "rule_4": {
            "results": [],
            "isTestMode": false
        },
        ...
    }
    ...
}

Within this nested "rules" object, I want to return true if results[0]["required"] = true AND "isTestMode" = false for any of the rules. The catch is that "rule_1", "rule_2", ... "rule_x" can have arbitrary names that aren't known in advance.

Is it possible to write a query that will iterate over all keys in '"rules"' and check if any one of them matches this condition? Is there any other way to achieve this?

If the keys were known in advance then I could do something like this:

WHERE 
    (JSON_ARRAY_LENGTH(JSON_EXTRACT(json, '$.rules.rule_1.results')) = 1 
        AND JSON_EXTRACT_SCALAR(json, '$rules.rule_1.results[0].required') = 'true' 
        AND JSON_EXTRACT_SCALAR(json, '$rules.rule_1.isTestMode') = 'false')
    OR (JSON_ARRAY_LENGTH(JSON_EXTRACT(json, '$.rules.rule_2.results')) = 1 
        AND JSON_EXTRACT_SCALAR(json, '$rules.rule_2.results[0].required') = 'true' 
        AND JSON_EXTRACT_SCALAR(json, '$rules.rule_2.isTestMode') = 'false')
    OR ...

Upvotes: 0

Views: 179

Answers (2)

Guru Stron
Guru Stron

Reputation: 142413

You can extract rules property and transform it to MAP(varchar, json) and process it:

WITH dataset AS (
    SELECT * FROM (VALUES   
       (JSON '{
    "rules": {
        "rule_1": {
            "results": [],
            "isTestMode": true
        },
        "rule_2": {
            "results": [],
            "isTestMode": true
        },
        "rule_3": {
            "results": [
                {
                    "required": true,
                    "amount": 99.31
                }
            ],
            "isTestMode": true
        },
        "rule_4": {
            "results": [],
            "isTestMode": false
        }
    }
}')
 ) AS t (json_value))

select cardinality(
    filter(
        map_values(cast(json_extract(json_value, '$.rules') as MAP(varchar, json))), -- trasnform into MAP and get it's values
        js -> cast(json_extract(js, '$.isTestMode') as BOOLEAN) -- check isTestMode
            AND cast(json_extract(js, '$.results[0].required') as BOOLEAN)  -- check required of first element of `results`
    )) > 0
from dataset

Which will give true for provided data.

Upvotes: 1

KOB
KOB

Reputation: 4545

I was able to solve this with a regex. Not ideal and would still like to know if this can be done using the built in JSON functions.

WHERE REGEXP_LIKE(json, '.*{"results":\[{"required":true,"amount":\d+.\d+"}],"isTestMode":false}.*')

Upvotes: 0

Related Questions