Tools.JsonExpand

The Tools.JsonExpand provider enables you to write a Luminesce query that parses a JSON document into a table of constituent data objects, much like the SQLite json.tree function.

Note: The LUSID user running the query must have sufficient access control permissions to use this provider. This should automatically be the case if you are the domain owner.

You can use this provider in conjunction with the Drive.RawText provider to read JSON from a text file stored in Drive and select particular objects for further processing (see example 3 below).

Basic usage

select * from Tools.JsonExpand where JsonString = <valid-json>; 

Query parameters

Tools.JsonExpand has parameters that enable you to filter or refine a query.

Note: The JsonString parameter is mandatory and must be passed a scalar variable containing valid JSON to parse.

To list available parameters, their data types, default values, and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, ParamDefaultValue, Description from Sys.Field where TableName = 'Tools.JsonExpand' and FieldType = 'Parameter'; 

Data fields

By default, Tools.JsonExpand returns a table of data populated with particular fields (columns). You can return just a subset of these fields if you wish.

To list fields available to return, their data types, whether fields are considered 'main', and an explanation for each, run the following query using a suitable tool:

select FieldName, DataType, IsMain, IsPrimaryKey, SampleValues, Description from Sys.Field where TableName = 'Tools.JsonExpand' and FieldType = 'Column'; 

Note: Fields marked 'main' are returned by queries that start select ^ from Tools.JsonExpand...

Examples

Imagine a JSON document like this:

{"menu": {
 "value": "File",
 "menuitem": [
     {"value": "New", "onclick": "CreateNewDoc()"},
     {"value": "Open", "onclick": "OpenDoc()"}
 ]
}}

Example 1: Parsing a JSON document

In this example, @@jsn is a scalar variable assigned the JSON document as a string (note json is a reserved word so @@json is not a valid variable name):

@@jsn = select '{"menu": { "value": "File", "menuitem": [ {"value": "New", "onclick": "CreateNewDoc()"}, {"value": "Open", "onclick": "OpenDoc()"} ] } }';
select * from Tools.JsonExpand where JsonString = @@jsn

The table of data returned by the query looks like this:

Example 2: Parsing part of a JSON document

In this example, the RootPath parameter takes a JSONPath expression that returns only the first menuitem object.

@@jsn = select '{"menu": { "value": "File", "menuitem": [ {"value": "New", "onclick": "CreateNewDoc()"}, {"value": "Open", "onclick": "OpenDoc()"} ] } }';
@@selection = select "$.menu.menuitem[0]";
select * from Tools.JsonExpand where JsonString = @@jsn and RootPath = @@selection

The table of data returned by the query looks like this:

Example 3: Parsing a file stored in Drive

In this example, the JSON document is stored as a text file in Drive. The Drive.RawText provider reads the file into a scalar variable, which is then passed as input to Tools.JsonExpand.

@@x = use Drive.RawText
--file=fileMenu.json
enduse;
select * from Tools.JsonExpand where JsonString = @@x

Example 4: Concatenating multiple files stored in Drive

In this example, Drive.RawText reads multiple files in a Drive folder into a table variable. The CROSS APPLY statement emulates a FOR EACH loop to concatenate the JSON in each file into a single table of data objects, each labelled with the name of the original source file. Note the JsonString parameter is still only passed a scalar variable: the single cell returned by a.Content from each file.

@a = use Drive.RawText
--file=json
--folderFilter=[A-aZ-z].json
--addFileName
enduse;

@x = select
   a.FileName,
   a.Content,
   jsn.*
from
   @a a
   cross apply
   (
       select
           c.*
       from
           Tools.JsonExpand c
       where
           JsonString = a.Content
   ) jsn
   ;

select FileName, [Key], Value, Type, Atom, Id, Parent, FullKey, Path from @x