Oracle’s JSON Path Expression (JPE ) uses specific characters such as dollar signs and dots to step through JSON structures. For example a JPE of $.NAME steps to the NAME key at the absolute top of a JSON structure. Whenever invalid syntax JPE syntax is encountered Oracle throws an error with a JZN prefix. Why that prefix? I suspect it is a shortened, stylized version of JSON. I also suspect the WTF prefix was already taken.
As I come across new JZN errors I will document them in this series. I will also poke fun at them. Because sometimes that alleviates any frustration and that is how I roll.
|Error Text||Question mark must be followed by parenthetical expression|
|Pig Latin||uestionqay arkmay ustmay ebay ollowedfay ybay arentheticalpay expressionyay|
|Erroneous JPE||‘$.mrns[*]?@.OS == 1’)|
|Correct JPE||‘$.mrns[*]?(@.OS == 1)’)|
Question marks denote that query criteria will follow. The start and end of the criteria must be surrounded by parenthesis so the parser knows where it begins and completes.
-- Correct Syntax SELECT 1 FROM json_patient WHERE JSON_EXISTS(patient_data,'$.mrns[*]?(@.OS == 1 )') --Incorrect Syntax SELECT 1 FROM json_patient WHERE JSON_EXISTS(patient_data,'$.mrns[*]?@.OS == 1 ')
Thanks for reading!