Skip to main content

All the standard indicium API's are expecting JSON and have JSON as output, also most of the 3rd party API's use JSON, therefore I think it would be useful to have some different JSON approaches and examples at one place, please feel free to add your own in the comments.

Creating JSON

When creating a simple JSON statement it would look something like this, this is without data from a table, but for example data you get in a process flow for 1 record and need to pass it to a JSON API.

--Having an example variable
declare @json nvarchar(max)

set @json = (
select 0 as Value_1
,1 as Value_2
,1 as Value_3
,1 as Value_4
,0 as Value_5
,0 as Value_6
,0 as Value_7
for json path, without_array_wrapper
)

The output would look like this:

{
"Value_1": 0,
"Value_2": 1,
"Value_3": 1,
"Value_4": 1,
"Value_5": 0,
"Value_6": 0,
"Value_7": 0
}

When you leave out the "without_array_wrapper” you will treat the set as an array and the result would look like this, where the difference is in the square brackets, this would be useful when you have a recurring set in 1 or multiple records, lines in header for example, or when returning a table:

"
{
"Value_1": 0,
"Value_2": 1,
"Value_3": 1,
"Value_4": 1,
"Value_5": 0,
"Value_6": 0,
"Value_7": 0
}
]

You can also select a set of data into a json from a table, for example employees:

set @json = (
select e.employee_id
,e.employee_name
from employee e
for json path
)

The result would look like this:

"
{
"employee_id": 1,
"employee_name": "Bibbye Erebia"
},
{
"employee_id": 5,
"employee_name": "Germana Presti"
},
{
"employee_id": 4,
"employee_name": "Josphine Mcquinn"
},
{
"employee_id": 6,
"employee_name": "Macy Agan"
},
{
"employee_id": 2,
"employee_name": "Rubi Shirley"
},
{
"employee_id": 3,
"employee_name": "Tessa Cahalan"
}
]

You can also give the employees and the column names different identifiers to create groups of data, the SQL code would look like this: 

set @json = (
select e.employee_id as aemployee.identifier]
,e.employee_name as aemployee.fullname]
from employee e
for json path
)

With the result: 

o
{
"employee": {
"identifier": 1,
"fullname": "Bibbye Erebia"
}
},
{
"employee": {
"identifier": 5,
"fullname": "Germana Presti"
}
},
{
"employee": {
"identifier": 4,
"fullname": "Josphine Mcquinn"
}
},
{
"employee": {
"identifier": 6,
"fullname": "Macy Agan"
}
},
{
"employee": {
"identifier": 2,
"fullname": "Rubi Shirley"
}
},
{
"employee": {
"identifier": 3,
"fullname": "Tessa Cahalan"
}
}
]

 

As mentioned before you can also have multiple detail records within 1 record, in my example I have a table with the functions and names and a link table for which employee has which function, the SQL query would look like this: 

set @json = (
select e.employee_id
,e.employee_name
,(select f.function_name
,null as Example_Null_Value
from employee_function ef
join function] f
on f.function_id = ef.function_id
where ef.employee_id = e.employee_id
for json path, include_null_values
) as 'function'
from employee e
for json path
)

Take notice of the “include_null_values” statement, without this statement the null results would not be send over to reduce data, which in most cases is desirable.
The JSON output of this query will look like this: 

g
{
"employee_id": 1,
"employee_name": "Bibbye Erebia",
"function": "
{
"function_name": "Production",
"Example_Null_Value": null
},
{
"function_name": "R&D",
"Example_Null_Value": null
}
]
},
{
"employee_id": 5,
"employee_name": "Germana Presti",
"function": "
{
"function_name": "Purchasing",
"Example_Null_Value": null
}
]
},
{
"employee_id": 4,
"employee_name": "Josphine Mcquinn",
"function": "
{
"function_name": "Production",
"Example_Null_Value": null
}
]
},
{
"employee_id": 6,
"employee_name": "Macy Agan",
"function": "
{
"function_name": "Marketing",
"Example_Null_Value": null
}
]
},
{
"employee_id": 2,
"employee_name": "Rubi Shirley",
"function": "
{
"function_name": "Marketing",
"Example_Null_Value": null
},
{
"function_name": "HR",
"Example_Null_Value": null
}
]
},
{
"employee_id": 3,
"employee_name": "Tessa Cahalan",
"function": "
{
"function_name": "R&D",
"Example_Null_Value": null
},
{
"function_name": "Purchasing",
"Example_Null_Value": null
},
{
"function_name": "Accounting",
"Example_Null_Value": null
}
]
}
]

Modifying JSON

After creating (or receiving) JSON sometimes its needed to modify the JSON, this can be done by querying JSON, modifying and then creating a new JSON again, but you could also use the “json_modify” function from SQL.

--Declaring the JSON
declare @json nvarchar(max)

set @json = (
select 0 as Value_1
,1 as Value_2
for json path, without_array_wrapper
)
{"Value_1":0,"Value_2":1}
--Adjusting the Value_1 to 200
set @json=json_modify(@json,'$.Value_1',200)
{"Value_1":200,"Value_2":1}
--Adding a new value to the JSON, named "Value_New" with as value "New Value"
SET @json=json_modify(@json,'$.Value_New','New Value')
{"Value_1":200,"Value_2":1,"Value_New":"New Value"}

For more information about the “json_modify” function check the Microsoft website: https://learn.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql

Note: be careful with the json_modify, for small JSON variables its fast, for bigger sets it pretty intensive.

Checking JSON

In SQL there is a function called “isjson” to check whether a JSON is a valid JSON, you could use it with the following code: 

if isjson(@json) = 1
begin
--Valid JSON, so we can continue.
end
else
begin
--Invalid JSON, raise an error.
end

This will result in a “1” if the JSON is valid, for more arguments within the “isjson” function please refer to Microsoft ( https://learn.microsoft.com/en-us/sql/t-sql/functions/isjson-transact-sql )

Reading JSON

So, now we know how to create, modify and validate JSON, we also need to know how to read JSON into SQL in order to process the incoming data.

The examples are based on this JSON:

{
"Value_1": 0,
"Value_2": 1,
"Value_3": 1,
"Value_4": 1,
"Value_5": 0,
"Value_6": 0,
"Value_7": 0
}

A basic way to read JSON is with the openjson statement, it looks like this:

select *
from OPENJSON(@json) j

When using a JSON with only 1 record, the result will be a row for every value in the JSON, for example:

Key Value Type
Value_1 0 2
Value_2 1 2
Value_3 1 2
Value_4 1 2
Value_5 0 2
Value_6 0 2
Value_7 0 2

 

The “Type” field gives back the datatype, the following datatypes are valid:
 

0 null
1 string
2 number
3 true/false
4 array
5 object

 

When you want all (or a part) of the values in columns for the record you can use the “with” statement. The “with” statement can be used to retrieve 1 or more values (existing or not!) from a JSON variable, the first part of the statement is the alias you want to give the value, after that the datatype (use proper datatypes, and not just only nvarchar(max)!) and the last is the path to the data, looking something like:

select *
from OPENJSON(@json)
with (Value_one int '$.Value_1'
,Value_two int '$.Value_2'
,Value_unknown int '$.Value_Unknown') j

Which result in: (Since the Value_Unknown is not in there, it will show as a “null” value, it will not give an error.)

Value_one Value_two Value_unknown
0 1 null

 

For the next examples we use the following query to generate the JSON:

set @json = (
select e.employee_id
,e.employee_name
,(select f.function_name
from employee_function ef
join /function] f
on f.function_id = ef.function_id
where ef.employee_id = e.employee_id
for json path
) as 'function'
from employee e
for json path
)

In this JSON we have employee_id, employee_name and in an array the functions. To retrieve alle the information we can use the following statement:

select *
from openjson(@json)
with (employee_id int '$.employee_id'
,employee_name varchar(35) '$.employee_name'
,functions nvarchar(max) '$.function' as json) j

The result will look like this:

employee_id employee_name functions
1 Bibbye Erebia h{"function_name":"Production"},{"function_name":"R&D"}]
5 Germana Presti /{"function_name":"Purchasing"}]
4 Josphine Mcquinn d{"function_name":"Production"}]
6 Macy Agan n{"function_name":"Marketing"}]
2 Rubi Shirley ]{"function_name":"Marketing"},{"function_name":"HR"}]
3 Tessa Cahalan <{"function_name":"R&D"},{"function_name":"Purchasing"},{"function_name":"Accounting"}]

 

If you want to retrieve the function_names you can do with the following query: (For now we only use employee_id 1 to reduce the resultset.)

select j.employee_id, j.employee_name, f.function_name
from openjson(@json)
with (employee_id int '$.employee_id'
,employee_name varchar(35) '$.employee_name'
,functions nvarchar(max) '$.function' as json) j
cross apply openjson(j.functions)
with (function_name varchar(100) '$.function_name')f
where j.employee_id = 1

Note: Please be aware that in the cross apply (could also be a outer apply) we use the alias j.functions, which results in an automatic “join” for the correct records, don’t use the variable @json again!

With the result:

employee_id employee_name function_name
1 Bibbye Erebia Production
1 Bibbye Erebia R&D

 

When there is an array within a single record you can also choose to only get 1 specific record, this you can achieve by using square brackets, in this case behind the functions, c0] stands for the first record, the “Production” function_name.

select j.employee_id, j.employee_name, f.function_name
from openjson(@json)
with (employee_id int '$.employee_id'
,employee_name varchar(35) '$.employee_name'
,functions nvarchar(max) '$.functionc0]' as json) j
cross apply openjson(j.functions)
with (function_name varchar(100) '$.function_name')f
where j.employee_id = 1

Note: Openjson is pretty CPU intensive, which is not a big problem since we need it, but if you need the openjson query in multiple queries in your procedure, please consider putting the result in a (temp) table before processing.

Cases

Let’s say you are retrieving information from a message box API and getting the following result, and you need the URL from the response JSON to call in the next http request:

{
"_embedded": {
"messages": {
"messageBox": "box_name",
"id": "ABC-ABC-123-123",
"_links": {
"self": {
"href": "https://website.com/messageBoxes//messages/ABC-ABC-123-123"
},
"nativeDocumentResource": {
"href": "https://website.com/api/edi/v1/messageBoxes//messages/ABC-ABC-123-123/documents/123-123-123-123/content"
}
}
}
}
}

When using openjson you can give a “default path” to look into, we want to look into the “_embedded” and “messages” as a default, so the “with” statement will only look for everything within that path, for example:

SELECT *
FROM openjson( @json,'$._embedded.messages' )
with (NativeDocumentURL nvarchar(1000) '$._links.nativeDocumentResource."href"')

If you have a single record JSON and you only need 1 value you can also extract the data with a “JSON_VALUE” function:

SELECT JSON_VALUE(@json,'$._embedded.messages._links.nativeDocumentResource.href') AS nativeDocumentResource

Note: Please consider using a temp table and openjson if you want to compare / fetch multiple values and or rows, do not use json_value for this purpose!


 

We have a procedure with only 1 JSON parameter, being called from different places, this JSON parameter contains a set of fixed parameters and another JSON parameter to give a set of flexible parameters (for example to replace in a text), the JSON would look something like this:

{
"json": {
"parameter_1": "Fixed 1",
"parameter_2": "Fixed 2",
"extra_parameters_json": ""{\"Parameter_1\": \"Flexible 1\",\"Parameter_2\": \"Flexible 2\"}]",
"name": "Some Name"
}
}

To be able to retrieve all the parameters needed we could use a query like this:

select p.parameter_1
,p.parameter_2
,p.name
,x.Parameter_1
,x.Parameter_2
from openjson(@json)
with (json nvarchar(max) as json) j
cross apply openjson(j.json)
with (parameter_1 nvarchar(25) '$.parameter_1'
,parameter_2 nvarchar(15) '$.parameter_2'
,extra_parameters_json nvarchar(max) '$.extra_parameters_json'
,name nvarchar(25) '$.name'
) p
cross apply openjson(p.extra_parameters_json)
with (Parameter_1 nvarchar(15) '$.Parameter_1'
,Parameter_2 nvarchar(15) '$.Parameter_2'
) x

 

When using the HTTP connector, sometimes you need to create you own headers, as seen here: https://docs.thinkwisesoftware.com/docs/sf/process_flows_connectors#http-connector

Recreating a header like that could be achieved by the following query:

select *
from (
select 'Header1' as okey]
,'Value1' as =value]
union all
select 'Header2' as akey]
,'Value2' as avalue]
) x
for json path

Result: 

s{"key":"Header1","value":"Value1"},{"key":"Header2","value":"Value2"}]

 

For a API endpoint the headers look a little bit different, as seen here: https://docs.thinkwisesoftware.com/docs/sf/process_flows#variables-and-properties

This can be achieved by the following query:

select (select json_query('a"value1","value2"]')) as Header1
,(select json_query('u"value"]')) as Header2
for json path, without_array_wrapper

Result:

{"Header1":o"value1","value2"],"Header2":<"value"]}

 

Cheers! You just saved me an entire chapter for the API integrations manual I was writing.


Very interesting and helpfull, Robbert! Great article.


I’d like to add to this that, once json is stored in a variable, it no longer is a json datatype (like XML is an actual datatype). It is simply text.

This causes issues when concatenating these json segments together in a nested json object.

--Having an example variable
declare @json nvarchar(max)

set @json = (
select 0 as Value_1
,1 as Value_2
,1 as Value_3
,1 as Value_4
,0 as Value_5
,0 as Value_6
,0 as Value_7
for json path, without_array_wrapper
)

-- This results in escaped nested json
select
'hello' as message,
@json as nested_data
for json path, without_array_wrapper

The code above yields the following result:

{
"message": "hello",
"nested_data": "{\"Value_1\":0,\"Value_2\":1,\"Value_3\":1,\"Value_4\":1,\"Value_5\":0,\"Value_6\":0,\"Value_7\":0}"
}

The variable is simply seen as a string and is escaped in the nested json. This can be resolved as following:

--Having an example variable
declare @json nvarchar(max)

set @json = (
select 0 as Value_1
,1 as Value_2
,1 as Value_3
,1 as Value_4
,0 as Value_5
,0 as Value_6
,0 as Value_7
for json path, without_array_wrapper
)

-- This gives a proper result
select
'hello' as message,
json_query(@json) as nested_data
for json path, without_array_wrapper

Using json_query turns it into a json object again, as if it was the result of a for json path.

The result will be as following:

{
"message": "hello",
"nested_data": {
"Value_1": 0,
"Value_2": 1,
"Value_3": 1,
"Value_4": 1,
"Value_5": 0,
"Value_6": 0,
"Value_7": 0
}
}

 

 


Another useful function when assembling json is STRING_ESCAPE, this way you can clean up your input when assembling json, for example when you want to pass HTML strings into your json array.

Like so:

select eo.email_subject as subject
,STRING_ESCAPE(eo.email_body,'json') as body
from email_object eo
for json auto

 


Robbert, great blog.

 

I usually use OPENJSON a little bit different. In most of the time I need all JSON-content and therefor I skip the JSON path part, which (in my opinion) makes the json queries more readable, and easier to write. Example:

declare @json nvarchar(max)

set @json = '{
"post code": "7328",
"country": "Netherlands",
"country abbreviation": "NL",
"places": :
{
"place name": "Apeldoorn",
"longitude": "5.9694",
"state": "Gelderland",
"state abbreviation": "GE",
"latitude": "52.21"
}
]
}'

set @json = ' ' + @json + ']'

select a.apost code]
,a.country
,a.acountry abbreviation]
,b.bplace name]
,b.longitude
,b.state
,b.bstate abbreviation]
,b.latitude
from openjson(@json)
with ( post code] nvarchar(100),
country nvarchar(100),
country abbreviation] nvarchar(100),
places nvarchar(max) as json
) a
cross apply openjson(a.places)
with ( place name] nvarchar(100)
,longitude numeric(10,6)
,state nvarchar(100)
, state abbreviation] nvarchar(100)
,latitude numeric(10,4)
) b

 


As for creating nested JSON, I really like the following method with simply joining tables:


set nocount on

declare @persons as table
(
person_id int primary key,
person_name varchar(20)
)

declare @pets as table
(
pet_owner int, -- in real tables, this would be a foreign key
pet_id int primary key,
pet_name varchar(10)
)

insert into @persons (person_id, person_name)
values (2, 'Jack'),
(3, 'Jill')

insert into @pets (pet_owner, pet_id, pet_name)
values (2, 4, 'Bug'),
(2, 5, 'Feature'),
(3, 6, 'Fiend')


-- Result with hierarchy in json structure (AUTO)
-- In AUTO mode, the structure of the JOIN CLAUSE determines
-- the format of the JSON output.
select persons.person_id,
persons.person_name,
pets.pet_id,
pets.pet_name
from @persons persons
join @pets pets
on pets.pet_owner = persons.person_id
for json auto, root('persons')

The result here is: 

{
"persons":
{
"person_id": 2,
"person_name": "Jack",
"pets":
{
"pet_id": 4,
"pet_name": "Bug"
},
{
"pet_id": 5,
"pet_name": "Feature"
}
]
},
{
"person_id": 3,
"person_name": "Jill",
"pets":
{
"pet_id": 6,
"pet_name": "Fiend"
}
]
}
]
}

 


Reply