cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

Question

In instances where tables within SQL Server contain JSON objects within a column you can use the import SQL query editor feature in Sisense to flatten these objects into individual fields.

Community_Admin_0-1634390519573.png

 

Community_Admin_1-1634390519543.png

For example:

Community_Admin_2-1634390519666.png

Can be turned into:

Community_Admin_3-1634390519659.png
Answer

 

To accomplish this, SQL Server has native functions that are capable of parsing json objects and flattening them into individual fields.

The below code uses the JSON_Value extracts a scalar value from a JSON string in which the first parameter takes an expression which is typically the name of a variable or column that contains JSON text. The second parameter takes a JSON path that specifies the property to extract. The default path mode is lax mode, which takes the syntax '$.<keyname>'.

Below is the SQL used to flatten the JSON values in the sample data.

Here is the SQL code you can use:

select SalesOrderNumber

, JSON_Value(JSONValue,'$.CustomerID') as CustomerID

, JSON_Value(JSONValue,'$.OrderDate') as OrderDate

, JSON_Value(JSONValue,'$.TotalDue') as TotalDue

, JSON_Value(JSONValue,'$.ShipMethodID') as ShipMethodID

, JSON_Value(JSONValue,'$.TerritoryID') as TerritoryID

, JSON_Value(JSONValue,'$.SalesPersonID') as SalesPersonID 

from OrderHeaderJSON

Other useful functions regarding working with JSON in SQL Server can be found here:

Version history
Last update:
‎10-16-2021 06:22 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request