Flattening JSON Objects In SQL Server

[intapiuser]

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.

For example:

Can be turned into:

Solution:

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.

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:

• ISJSON (Transact-SQL) tests whether a string contains valid JSON.
• JSON_VALUE (Transact-SQL) extracts a scalar value from a JSON string.
• JSON_QUERY (Transact-SQL) extracts an object or an array from a JSON string.
• JSON_MODIFY (Transact-SQL) changes a value in a JSON string.