2021 – JSON Path Expressions And Dynamic SQL

You say passing, I say using…let’s call the whole thing off

I use colons, you use dollar signs, let’s call the whole thing off

The previous post presented some pretty complex code including:

  • JSON Path Expressions (dollar signs, ampersands, etc)
  • Dynamic SQL (colons and the USING clause)
  • JSON_EXISTS (PASSING clause)

I promised an explanation of the code so here it is. I’ll start with a completely hard coded, non dynamic version and work forwards (or is it backwards?) to explain each detail. Let get into the code now!

Hardcoded Everything

The hard coded version simply queries JSON for entries in the mrns array containing an OS element with a value of 1 and MRN element with a value of MRN123.

SELECT JSON_VALUE(patient_data,'$.pname')
  FROM json_patient
 WHERE JSON_EXISTS(patient_data,'$.mrns[*]?(@.OS == 1 && @.MRN == "MRN123")');

JSON Path Expression

Lets dissect the JSON Path Expression (JPE) portion highlighted in red above. JPE is processed from left to right so we’ll start on the very left. The $ means start at the absolute top of the JSON structure and .mrns means move down one level to then mrns element.

The first line of the code snippets below is the JPE. The second line is JSON data. The red highlighted portion shows the corresponding navigation between the two.

$.mrns[*]?(@.OS == 1 && @.MRN == "MRN123")
{"pname":"Patient 1","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}');

[*] means search in all elements of the mrns array.

$.mrns[*]?(@.OS == 1 && @.MRN == "MRN123")
{"pname":"Patient 1","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}');

Next we specify the criteria to find entries in the mrns array using the question mark (?). The criteria has two parts:

The OS element relative to where we are now must be 1

'$.mrns[*]?(@.OS == 1 && @.MRN == "MRN123")'
{"pname":"Patient 1","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}')

And (which is denoted by the double ampersand) part 2 which is:

The MRN element relative to where we are now must be MRN123

'$.mrns[*]?(@.OS == 1 && @.MRN == "MRN123")'
{"pname":"Patient 1","mrns":[{"OS":1,"MRN":"MRN123"},{"OS":2,"MRN":"MRN456"}]}')

The row for Patient 1 satisfies the criteria and is returned.

Note that the phrase I used was”relative to where we are now.” This relativity is denoted by the @ sign. It means carry on from where we absolutely were at ($.mrns[*]) and look for the OS (@.OS) and MRN (@.MRN) values at that point.

Passing Parameters To JSON_EXISTS

Next we’ll pass parameters to JSON_EXISTS with the PASSING clause. Here is a hardcoded example.

SELECT JSON_VALUE(patient_data,'$.pname')
  FROM json_patient
 WHERE JSON_EXISTS(patient_data,'$.mrns[*]?(@.OS == $OS && @.MRN == $MRN)'
       PASSING 1 AS "OS", 'MRN123' AS "MRN");

The PASSING clause uses KEY/VALUE pairs (because this is JSON don’t forget) separated by commas. In this example 1 is specified for the OS parameter and MRN123 is provided for the MRN parameter. The parameter names match up with dollar sign prefixed ($OS and $MRN) items in the JPE.

Using Parameters For Dynamic SQL

Next we’ll set things up for dynamic SQL. First change the PASSING clause to sequential numbers prefixed with colons like this:

PASSING :1 AS "OS", :2 AS "MRN"

Then specify the USING clause in an EXECUTE IMMEDIATE call

EXECUTE IMMEDIATE v_sql INTO v_ret_val USING p_os, p_mrn;

Here’s the final function.

CREATE OR REPLACE FUNCTION get_pname ( p_os NUMBER,
                                       p_mrn VARCHAR2 )
                           RETURN VARCHAR2 IS
  v_sql VARCHAR2(1000);
  v_ret_val VARCHAR2(100);
BEGIN
  v_sql := 'SELECT JSON_VALUE(patient_data,''' ||
           '$.pname' || ''') FROM json_patient WHERE ';
  v_sql := v_sql || 'JSON_EXISTS(patient_data,''' || 
           '$.mrns[*]?(@.OS == $OS && @.MRN == $MRN)' || '''' || ' ';
  v_sql := v_sql || 'PASSING :1 AS "OS", :2 AS "MRN")';
  EXECUTE IMMEDIATE v_sql INTO v_ret_val USING p_os, p_mrn;
  RETURN v_ret_val;
END;

And an example call.

BEGIN
  DBMS_OUTPUT.PUT_LINE(get_pname(1,'MRN123'));
END;
Patient 1

In point form the whole interaction is described as:

  • 1 and MRN123 are passed to the get_pname function as parameters named p_os and p_mrn
  • SQL is assembled including JSON_EXISTS and two parameters named OS and MRN ($OS and $MRN)
  • The SQL is executed USING p_os and p_mrn which are PASSED into the JPE

Or from a clause point of view:

  • PASSING sends parameters to JSON_EXISTS
  • USING sends parameters to Dynamic SQL

And from the prefix point of view:

  • Function parameters get passed to…
  • …colon prefixed sequential numbers that get passed…
  • …to dollar signed prefixed parameters

Thanks for reading!

Leave a Comment