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:

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"]}