'Unable to access data in Postgres query where clause

I am dealing with the following JSON in a Postgres database column called steps in a table called tCampaign :

[
    {
        "name":"Step 1",
        "stepReference":"01e9f7c0-bc79-11eb-ab6f-2fa1cb676e38",
        "rewardConditions": [
            {
                "conditionDefinitions": [
                    {
                        "instanceId":"01805260-0818-4e99-e5b1-5820d1b133cd",
                        "type":"registration",
                        "properties": null, 
                        "name": "Registration"
                    },
                    {
                        "instanceId":"01e115c3-5e56-437a-5d13-6c04281e9588",
                        "type":"optIn",
                        "properties": null,
                        "name":"Opt In"
                    }
                ],
                "rewardDefinitions":[
                    {
                        "instanceId":"01c82190-1d56-44f9-474a-513732302e28",
                        "type":"sportsReward",
                        "properties": {"activation": {"type": "onReward"}, "betFlavour": "SPORTS", "channels": ["__use_campaign_restrictions__"], "expiry": {"offset": {"days": "02", "hours": "00", "minutes": "00", "seconds": "00"}, "type": "relative"}, "inRunning": "-", "maxReward": {"USD": "1"}, "minimumOdds": "", "oddsInput": {"minimumOdds": {"american": "", "european": ""}}, "retail": "offBetBuild", "returnStakeOnPayout": "false"},
                        "name":"Freebet",
                        "calculator":{"type":"fixed","value":"100"}
                    }
                ]
            }
       ]
    }, 
    {
        "name" : "Step 2",
        "stepReference" : "01daa4a0-bc79-11eb-ab6f-2fa1cb676e38",
        "rewardConditions": [
            {
                "conditionDefinitions": [
                    {
                        "instanceId" : "01fb15ae-01d0-49e1-966a-8ff438e9a191",
                        "type" : "genericSportsBet",
                        "properties" : {"betFlavour": "SPORTS", "betTrackEventThreshold": "10", "betTypes": [ "SGL" ], "builderBetOption": "ALL", "channels": [ "__use_campaign_restrictions__" ], "currencyThresholdMap": { "USD": "1" }, "eventHierarchySelection": { "categories": [], "classes": [], "events": [], "marketTemplates": [], "markets": [ "5824" ], "retrobetEventIds": [ "1200" ], "selections": [], "selectionsMarket": [], "types": [] }, "eventHierarchySelectionUI": { "markets": [ { "id": 5824, "mapping": [], "name": "Match Result", "parentId": 1200, "parentParentId": 5, "path": [ "Category: |England|", "Class: |England Premier League|", "Type: |GK Team K| |vs| |GK Team L|" ], "selectionMapper": false, "settled": "N", "startTime": "2021-05-31 11:15:00", "status": "A" } ] }, "inRunning": "-", "legTypes": [ "WIN" ], "metOnSettlement": false, "minOdds": "", "oddsInput": { "minOdds": { "american": "", "european": "" } }, "priceTypes": [ "LP" ]}, 
                        "name" : "Sports Bet"
                    }
                ],
                "rewardDefinitions":[
                    {
                        "instanceId" : "0110eb70-44f9-4d57-40bb-09ff4169136c",
                        "type" : "sportsReward",
                        "properties" : {"activation": {"type": "onReward"}, "betFlavour": "SPORTS", "channels": ["__use_campaign_restrictions__"], "expiry": {"offset": {"days": "02", "hours": "00", "minutes": "00", "seconds": "00"}, "type": "relative"}, "inRunning": "-", "maxReward": {"USD": "2"}, "minimumOdds": "", "oddsInput": {"minimumOdds": {"american": "", "european": ""}}, "retail": "offBetBuild", "returnStakeOnPayout": "false"},
                        "name" : "Freebet",
                        "calculator" : {"type":"fixed","value":"100"}
                    }
                ]
            }
       ]
    }
]

and have written the following query to extract properties from conditionDefinitions :

select conditionDefinitions->'properties'  as properties from tcampaign cmp
LEFT JOIN LATERAL json_array_elements(steps) singleStep ON true 
LEFT JOIN LATERAL json_array_elements(singleStep->'rewardConditions') rewardConditions on TRUE
LEFT JOIN LATERAL json_array_elements(rewardConditions->'conditionDefinitions') conditionDefinitions  on TRUE
where properties is not null ;

but I get the following error :

ERROR:  column "properties" does not exist
LINE 5: where properties is null ;

If I remove the where clause the query runs fine. Why do I not have access to properties in the where clause? Because I can see results coming back if I remove the WHERE clause, so the query does have results



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source