JSON (JavaScript object notation) is an unstructured format for storing and retrieving data within files and databases. It offers the flexibility of an unstructured data format like XML without the rigid schema binding required typically when using XML datatype. JSON is being increasingly adopted in a number of technology stacks for this exact reason. With the variety of data that we capture nowadays it is not always possible to create a well-defined normalized database structure to store this information. At least initially there is a need to work with the data in its unstructured format while enough examples are available so that standardization may occur. It is in this context that I have found myself using JSON. The requirement when it reaches us is too vague and doesn’t have enough details for us to design and normalize database. However, it is also not practical to wait until all the requirements are fleshed out to create a proof of concept for the project. In such cases JSON works as a decent compromise that can marry normalized database structure with the unstructured input making both available for querying. In this post we explore some basic examples of using JSON and querying it using TSQL code.
Check if a text is valid JSON
One of the first things we want to do when working with JSON strings is to check if it is valid. The below code shows how to TSQL function isJSON() to achieve this.
DECLARE @json VARCHAR(100) = '{"name":"Jayanth"}'
-- write query to check if the json is in the correct format using ISJSON function
SELECT ISJSON(@json) AS IsJsonNotice I am using GitHub Copilot in this example, the goal here is to show you that working with JSON need not be difficult.
Extract the name from JSON String
The next thing we want to do most of the time is extract a simple element from the JSON. This would be like reading a column from within a single table. To achieve this we need to use the JSON_VALUE field as shown below.
DECLARE @json VARCHAR(100) = '{"name":"Jayanth"}'
-- write query to check if the json is in the correct format using ISJSON function
SELECT ISJSON(@json) AS IsJson
-- extract the name from the above json
SELECT JSON_VALUE(@json, '$.name') AS Name
Extract Multiple fields from JSON string
As an extension of the above we explore how to extract multiple fields from within the same JSON String as shown below, once again we simply use the JSON_VALUE function as needed to query each column individually.
declare @json varchar(100) ='{"name":"Jayanth", "age": 25, "city":"Bangalore"}'
-- extarct the name from the above json , also age and city
select
JSON_VALUE(@json, '$.name') as
name,
JSON_VALUE(@json, '$.age') as age,
JSON_VALUE(@json, '$.city') as city
Extracting data from nested object in JSON
Occasionally the data is stored in a slightly complex format such as a nested object within the JSON. A nested object can be thought of as a join between a PK table and the FK table where the contents of the FK table are stored in the nested object and repeated for each PK attribute. For example, in the below JSON file a single customer can have multiple Phone numbers or an address line can have multiple elements. Notice that the phone numbers are stored in an array using [ ] while the address is saved as a object using { }. An array can have more than one object.
DECLARE @json VARCHAR(1000) = '{
"firstName": "John",
"lastName": "Smith",
"age": 25,
"address":
{
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021"
},
"phoneNumber":
[
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "fax",
"number": "646 555-4567"
}
]
}' -- write a query to extract firstname and address from the above json
SELECT JSON_VALUE(@json, '$.firstName') AS FirstName
, JSON_VALUE(@json, '$.address.streetAddress') AS StreetAddress
, JSON_VALUE(@json, '$.address.city') AS City
, JSON_VALUE(@json, '$.address.state') AS STATE
, JSON_VALUE(@json, '$.address.postalCode') AS PostalCode
Extract data from Array within JSON object
After extracting address as shown above, we are faced with a slightly more difficult scenario of extracting objects from within the array. In the below example we explore how to extract an object when the position of the object within the array is known. This is done by passing the ordinal position of the object [0] (first object) to the JSON_VALUE function.
DECLARE @json VARCHAR(1000) = '{
"firstName": "John",
"lastName": "Smith",
"age": 25,
"address":
{
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021"
},
"phoneNumber":
[
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "fax",
"number": "646 555-4567"
}
]
}'
-- write a query to extarct phone number of type home from the json along with firtname and lastname and age
SELECT JSON_VALUE(@json, '$.firstName') AS FirstName
, JSON_VALUE(@json, '$.lastName') AS LastName
, JSON_VALUE(@json, '$.age') AS Age
, JSON_VALUE(@json, '$.phoneNumber[0].number') AS HomePhoneNumber
Extract all the attributes within the array even when the number of attributes is uncertain
Sometimes it is not possible to know how many objects are present in a certain array and therefore we can’t hardcode the ordinal position as shown in the above query. In such cases we need to be a little more creative with the use of the JSON_QUERY function to extract the data we need out of the original JSON string before it can be queried. Notice how we extract the array using OPENJSON function.
DECLARE @json VARCHAR(1000) = '{
"firstName": "John",
"lastName": "Smith",
"age": 25,
"address":
{
"streetAddress": "21 2nd Street",
"city": "New York",
"state": "NY",
"postalCode": "10021"
},
"phoneNumber":
[
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "fax",
"number": "646 555-4567"
}
]
}'
-- write a query to extarct phoenumbers of all types from the above json even when the number of types is not certain
-- output should be like this
-- type number
-- home 212 555-1234
-- fax 646 555-4567
SELECT *
FROM openjson(@json, '$.phoneNumber') WITH (
[type] VARCHAR(10) '$.type'
, [number] VARCHAR(20) '$.number'
)
OUTER APPLY (
SELECT JSON_VALUE(@json, '$.firstName') AS FirstName
) AS a
Some More examples
The unstructured nature of JSON allows us to store data in a number of different formats. In the below example we look at a different way to extract JSON from an Array.
CREATE TABLE #SampleJson (
Zipcode VARCHAR(100)
,Products VARCHAR(100)
)
INSERT INTO #sampleJson (
Zipcode
,Products
)
SELECT 1
,'{"Available":[1,2,3,4,5],"UnAvailable":[6,7,8]}'
UNION
SELECT 2
,'{"Available":[1,2],"UnAvailable":[10]}'
UNION
SELECT 3
,NULL
SELECT *
FROM #sampleJson
OUTER APPLY (
SELECT *
FROM openjson(Products, '$.Available')
) AS Available
