Subscribe to this thread
Home - General / All posts - extract json values in queries using RS
apo34 post(s)
#07-Sep-17 14:14

I'm trying to extract values out of a json formatted string in a table column using SQL.

I'm looking on the way of using the new sql functions available in RS but can't find a way to use those, especially the StringToJsonObjectValues

Any insight on this issue would be greatly appreciate


4,843 post(s)
#07-Sep-17 16:12

Have you used the transform dialog's JSON String template? See the example in the transform templates for text topic. You can use the Edit Query button to see what SQL it writes for you.

8,009 post(s)
#08-Sep-17 01:02

The key here is probably that

StringToJsonObjectValues(<json>): <table>

is a table function (a function retuirning a table).

That means it must (normally) be used with CALL syntax, and the resulting table must be unpacked inline using SPLIT (or passed to a function taking a table and returning a scalar).

Here is a trivial example. It assumes a component [Drawing Table] with a property 'FieldCoordSystem.Geom'.


    [mfd_id]--[Name], [Property], [Value],

    SPLIT CALL StringToJsonObjectValues([Value])

FROM [mfd_meta]

WHERE [Name] = 'Drawing Table'

AND [Property] = 'FieldCoordSystem.Geom'

This returns a table where each object encoded in the JSON string is returned in a separate record listing its name, type and value.


7,903 post(s)
#10-Sep-17 08:25

Example (pasting log from the command window, I ran the line with the '?' and the command window printed the result on the next line, the leading # is there to set up forum autoformatting):


> ? StringJsonValue('{ "FirstName""Chuck""LastName""Norris" }', 'LastName', false)

nvarchar: Norris

You could post example strings and tell what values you want to extract, and someone will likely help.

apo34 post(s)
#11-Sep-17 08:45

Thank's to your answers I get the extraction of my values as needed . The trick is that I have to remember the SPLIT CALL asked because of the table answer.

Definitely useful function


Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.