Skip to main content
Question

Check a key field exists in JSON

  • June 6, 2022
  • 6 replies
  • 605 views
V
Fan

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": [{
                "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! 

 

6 replies

Rishi
StreamSets Employee
Forum|alt.badge.img
  • RishiStreamSets Employee
  • StreamSets Employee
  • June 7, 2022

@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

S
A
S
Headliner
Forum|alt.badge.img+1

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

 

Srinivasan Sankar
Rishi
A
A
Discovered Fame
  • ana00159Discovered Fame
  • Discovered Fame
  • June 7, 2022

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?

A
Discovered Fame
  • ana00159Discovered Fame
  • Discovered Fame
  • June 7, 2022

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

S
Headliner
Forum|alt.badge.img+1

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
Bikram
Headliner
Forum|alt.badge.img+1
  • BikramHeadliner
  • Headliner
  • June 27, 2022

@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.

1 Attachments
Terms & ConditionsAccessibility statement