topic Re: sql JSON_VALUE query and spaces in fields in JSL in Discussions

sql JSON_VALUE query and spaces in fields in JSL

SampleTapir814 — Wed, 08 May 2024 21:27:32 GMT

I'm trying to query a table in an Oracle table with a field that is JSON. I am using the JSON_VALUE function in the sql. Some of the JSON fields contain spaces so I put double quotes around values and this works when running the query outside of JSL but throws an error running the query as shown below in jsl. Thanks in advance for any hep you can provide.

JSL:

dbc = create database connection("Driver={Oracle in InstantClient11_1};UID=xxxx;DBQ=yyyy;PWD=zzzz;");

user_sql ="select json_value(feature, '$."ball dimension"') as ball_dimension from blah";

user_dt = execute sql(dbc, user_sql);

Re: sql JSON_VALUE query and spaces in fields in JSL

jthi — Thu, 09 May 2024 05:49:15 GMT

You have to escape those quotes and usually these methods work (I would use option 1)

Names Default To Here(1);

// user_sql ="select json_value(feature, '$."ball dimension"') as ball_dimension from blah";

// Option 1
user_sql1 = "\[select json_value(feature, '$."ball dimension"') as ball_dimension from blah]\";

// Option 2
user_sql2 = "select json_value(feature, '$.\!"ball dimension\!"') as ball_dimension from blah";


// Print demo
Write("\!N" || user_sql1);
Write("\!N" || user_sql2);

https://www.jmp.com/support/help/en/18.0/#page/jmp/jsl-syntax-rules.shtml - Look for Double Quotes

Edit:

Just to add extra some explanation. If you want have double quotes in your string in JMP, you have to escape them (there are also other characters which must be escaped). You can escape single double quote by replacing it with \!" and if you wish to "auto-escape" everything in your string, wrap it with "\[...]\" instead of just "...".

Re: sql JSON_VALUE query and spaces in fields in JSL

SampleTapir814 — Mon, 13 May 2024 15:36:22 GMT

Thank you , Jarmo!