---
title: "Tools.JsonExpand"
slug: "toolsjsonexpand"
updated: 2024-08-21T14:05:38Z
published: 2024-08-21T14:05:38Z
canonical: "support.lusid.com/toolsjsonexpand"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://support.lusid.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Tools.JsonExpand

| **Type** | **Read/write** | **Author** | **Availability** |
| --- | --- | --- | --- |
| [Data provider](/v1/docs/what-are-a-data-provider-and-a-direct-provider) | Read | Finbourne | Provided with LUSID |

The Tools.JsonExpand provider enables you to write a [Luminesce query](/v1/docs/understanding-the-luminesce-sql-query-syntax) that parses a JSON document into a table of constituent data objects, much like the [SQLite json.tree function](https://www.sqlite.org/json1.html#jtree).

> [!NOTE]
> **Note:** The LUSID user running the query must have sufficient [access control permissions](/v1/docs/creating-policies-to-control-access-to-providers-for-different-luminesce-end-users) 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](/v1/docs/driverawtext) provider to read JSON from a text file stored in [Drive](/v1/docs/drive) and select particular objects for further processing (see example 3 below).

## Basic usage

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

## Query parameters

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

> [!NOTE]
> **Note:** The JsonString parameter is mandatory and must be passed a [scalar variable](/v1/docs/understanding-the-luminesce-sql-query-syntax#variables) 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](/v1/docs/what-tools-are-available-to-write-luminesce-queries):

```sql
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](/v1/docs/what-tools-are-available-to-write-luminesce-queries):

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

> [!NOTE]
> **Note:** Fields marked 'main' are returned by queries that start select ^ from Tools.JsonExpand...

## Examples

Imagine a JSON document like this:

```json
{"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):

```sql
@@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:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/b38c73a7-a25a-4679-a0f9-3ca153e4c90b.png)

### 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.

```sql
@@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:

![](https://cdn.document360.io/d575ad81-c0ed-4980-bbd1-d59ac5c3de82/Images/Documentation/e88edfc8-44cd-42a5-a58a-d4b6d9f09cf8.png)

### 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](/v1/docs/driverawtext) provider reads the file into a scalar variable, which is then passed as input to Tools.JsonExpand.

```sql
@@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](/v1/docs/understanding-the-luminesce-sql-query-syntax#for-each-loops) 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.

```sql
@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
```