Skip to main content

How can I check if a key field exists in nested JSON? For example I have a json like this,

    "Ticket": {
        "Price": {
            "Adult": "18",
            "Child": {
                "FromAge": "0",
                "ToAge": "12",
                "Price": "10"
            }]
        }
    }
}

From source I might get the json as follows as well without one among the key fields (child missing).

    "Ticket": {
        "Price": {
            "Adult": "18"
            
        }
    }
}

In this case how can check if key field exists and if not I want to introduce "Child": c{
                "FromAge": "0",
                "ToAge": "12",
                "Price": "10"
            }] to the json to standardise the structure.

 

How can I achieve this in StreamSets? If I couldn’t standardize, at least I want to identify the records with missing key in the json.

 

Thanks! 

 

@vicky20 did you check for Required Fields feature, A required field is a field that must exist in a record to allow it into the stage for processing. When a record does not include all required fields, it is processed based on the error handling configured for the pipeline

ref  https://docs.streamsets.com/portal/datacollector/latest/help/datacollector/UserGuide/Pipeline_Design/DroppingUnwantedRecords.html#concept_dnj_bkm_vq

Hi, 

If the JSON is coming as a field of type MAP, can you use, the following in an Expression Editor?

/doesChildKeyExists     =    ${record:value('/Ticket/Price/Child')}

/doesChildKeyExists is the new field that you are introducing in the Expression Editor.

---

In the next step, use a StreamSelector to filter out ones that do not have a “Child” key and ones that do, 

${empty(record:value('/doesChildKeyExists')}

${!empty(record:value('/doesChildKeyExists')}

 

Also, you could ignore the expression editor and use the following conditions in the StreamSelector,

${empty(record:value('/Ticket/Price/Child')}

${!empty(record:value('/Ticket/Price/Child')}

 

Hope this helps.

 

Thanks,

Srini

 

Thank you @Srinivasan Sankar. Will try out this. Also can we standardize the json structure if it’s missing? Something like adding the missing json part sending as null?

I tried this and its not working. I see that it’s checking for the value, instead it should check for key field.

Hi @ana00159 , a value will be returned ONLY is the key exists. Hence, if a value is returned it would mean the “key exists”. If the value is NULL the key does not exist.

Of course, there could be edge cases where the value of the key is NULL. You will need to confirm that and tweak the conditions. 

 

@Srinivasan Sankar 

 

can you please try the below code to check if the record exist or not

 

${record:exists('/Ticket.Price.Adult')}

 

Attached the pipeline for the json data provided by you.

Reply


Terms & Conditions