SQL Server Select to JSON Function

SQL Server SELECT to JSON function

Starting from SQL Server 2016 you can use for json:

declare @t table(id int, name nvarchar(max), active bit)
insert @t values (1, 'Bob Jones', 1), (2, 'John Smith', 0)

select id, name, active
from @t
for json auto

With older versions of SQL Server you can use for xml path, e.g.:

select '[' + STUFF((
        select 
            ',{"id":' + cast(id as varchar(max))
            + ',"name":"' + name + '"'
            + ',"active":' + cast(active as varchar(max))
            +'}'

        from @t t1
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') + ']'

Output:

[{"id":1,"name":"Bob Jones","active":1},{"id":2,"name":"John Smith","active":0}]

Convert select query results into Json

JSON AUTO would make quick work of this in but JSON support is available only in SQL Server 2016 and later, including Azure SQL Database. For a T-SQL solution in SQL 2014 and earlier, you'll need to build the JSON string yourself.

Below is an example that uses a FOR XML subquery to concatenate the result into JSON format and adds the outermost [ and ] for the array. Note that this does not handle characters that must be escaped in JSON (\ and ") so you'll need to use REPLACE to escape those if contained in your data.

SELECT '[' + STUFF((
    SELECT
          ',{'
        + '"Id":'+CAST(Id AS varchar(10)) + ','
        + COALESCE('"Name":"' + Name + '",','')
        + COALESCE('"About":"' + About + '",','')
        + COALESCE('"Age":'+CAST(Age AS varchar(10)) + ',','')
        + COALESCE('"AddressId":'+CAST(AddressId AS varchar(10)), '')
        + '}'
    FROM TestTable
    FOR XML PATH(''), TYPE).value('.', 'varchar(MAX)'),1,1,'')
    + ']';

Create json object and aggregate into json array in SqlServer

If I understand the question correctly, the following statements are possible soltion (of course, they are based on the example data and statements in the question):

How to create a single JSON object:

If you want to generate one single JSON object, you need to use FOR JSON PATh for each row in the OUTER APPLY statement with the appropriate path expression. JSON_QUERY() is needed, because it returns a valid JSON and FOR JSON doesn't escape special characters.

Tables:

CREATE TABLE Supplier (
   Id int,
   Description varchar(50),
   DateStart date
)
CREATE TABLE Products (
   Id varchar(5),
   SupplierId int,
   Description varchar(100),
   Price numeric(10, 2)
)
INSERT INTO Supplier (Id, Description, DateStart)
VALUES (1, 'Oracle', '19900505')
INSERT INTO Products (Id, SupplierId, Description, Price)
VALUES ('11111', 1, 'Database Manager', 149.99)
INSERT INTO Products (Id, SupplierId, Description, Price)
VALUES ('22222', 1, 'Chassi', 249.99)

Statement:

SELECT *
FROM "Supplier" s
OUTER APPLY(
    SELECT Products = JSON_QUERY((
       SELECT 
          p."Id" AS 'Product.Id', 
          p."Description" AS 'Product.Description', 
          p."Price" AS 'Product.Price'
       FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ))
    FROM "Products" as p
    WHERE p."SupplierId" = s."Id"
) sp ("Products")

Result:

Id  Description DateStart   Products
1   Oracle      1990-05-05  {"Product":{"Id":"11111","Description":"Database Manager","Price":149.99}}
1   Oracle      1990-05-05  {"Product":{"Id":"22222","Description":"Chassi","Price":249.99}}

How to aggregate JSON objects into an array:

By default FOR JSON creates a JSON array with one JSON object for each row. You only need to set a root key:

Statement:

SELECT *
FROM "Supplier" s
OUTER APPLY(
    SELECT p."Id", p."Description", p."Price"
    FROM "Products" p
    WHERE p."SupplierId" = s."Id"
    FOR JSON PATH
) sp("Products")

Result:

Id  Description DateStart   Products
1   Oracle      1990-05-05  [{"Id":"11111","Description":"Database Manager","Price":149.99},{"Id":"22222","Description":"Chassi","Price":249.99}]

SQL Server : SELECT JSON Column in this JSON Structure

You have two options to parse this JSON array:

• Using OPENJSON() with explicit schema once - to get the content of each item
• Using OPENJSON() twice - to get the index and the content of each item

JSON:

DECLARE @json varchar(max) = '
[
    {
        "A": 6, 
        "Status": 1
    },
    {
        "A": 3, 
        "Status": 0
    },
    {
        "A": 6, 
        "Status": 1
    },
    {
        "A": 7, 
        "Status": 0
    }
]'

Using OPENJSON() with explicit schema once:

SELECT A, Status
FROM OPENJSON(@json) WITH (
   A int,
   Status int
)

Result:

A   Status
6   1
3   0
6   1
7   0

Using OPENJSON() twice:

SELECT 
   j1.[key] AS Index,
   j2.A, j2.Status
FROM OPENJSON(@json) j1
CROSS APPLY OPENJSON(j1.[value]) WITH (
   A int,
   Status int
) j2 

Result:

Index   A   Status
0       6   1
1       3   0
2       6   1
3       7   0

Of course, you can always access an array item by index:

SELECT 
   JSON_QUERY(@json, '$[0]') AS Item,
   JSON_VALUE(@json, '$[0].A') AS A, 
   JSON_VALUE(@json, '$[0].Status') AS Status

Result:

Item                   A    Status
{"A": 6, "Status": 1}  6    1

Read Json Value from a SQL Server table

The JSON string is an array with a single item. You need to specify the array index to retrieve a specific item, eg :

declare @t table (json_val  nvarchar(4000))

insert into @t
values ('[{"prime":{"image":{"id":"123","logo":"","productId":"4000","enable":true},"accountid":"78","productId":"16","parentProductId":"","aprx":"4.599"}}]')

select JSON_VALUE(cast(json_val as varchar(8000)), '$[0].prime.aprx') as px
from @t

This returns 4.599

If you want to search all array entries, you'll have to use OPENJSON. If you need to do that though ...

Avoid JSON if possible

JSON storage is not an alternative to using a proper table design though. JSON fields can't be indexed, so filtering by a specific field will always result in a full table scan. Given how regular this JSON string is, you should consider using proper tables instead

Multiple SELECT statements into a single JSON

According to the accepted answer of FOR JSON PATH. how to not use escape characters on SQL Server's forum on MSDN:

FOR JSON will escape any text unless if it is generated as JSON result by some JSON function/query. In your example, FOR JSON cannot know do you really want raw JSON or you are just sending some free text that looks like JSON.

Properly defined JSON is generated with FOR JSON (unless if it has WITHOUT_ARRAY_WRAPPER option) or JSON_QUERY. If you wrap your JSON literal with JSON_QUERY it will not be escaped.

This answer got me to try the following code:

DECLARE @Json nvarchar(max) = 
(
    SELECT
     JSON_QUERY((
        SELECT 'Data1' AS [Data1], 'Data2' AS [Data2]

        FOR JSON PATH
        , INCLUDE_NULL_VALUES
        , WITHOUT_ARRAY_WRAPPER
    )) AS [Part1]
    ,
    JSON_QUERY((
        SELECT 'Text1' AS [Text1], 'Text2' AS [Text2]

        FOR JSON PATH
        , INCLUDE_NULL_VALUES
        , WITHOUT_ARRAY_WRAPPER
    )) AS [Part2]

    FOR JSON PATH
    , WITHOUT_ARRAY_WRAPPER
);

SELECT @Json;

As as it turns out - this is working like a charm. Results:

{
    "Part1": {
        "Data1": "Data1",
        "Data2": "Data2"
    },
    "Part2": {
        "Text1": "Text1",
        "Text2": "Text2"
    }
}

DB<>Fiddle

---

Update
Look what I found buried in official documentation:

To avoid automatic escaping, provide newValue by using the JSON_QUERY function. JSON_MODIFY knows that the value returned by JSON_MODIFY is properly formatted JSON, so it doesn't escape the value.

Why doesn't select using like query in json string?

In sql-server you need to escape the [] in a like, see: LIKE (Transact-SQL)

This works too (replacing the [ by and _, which matches any single character:

SELECT * 
FROM Table 
WHERE CODE_DESC LIKE REPLACE('%{"title:\["1","2","3"\]"}%','[','_')

How to get data from json column in mssql

From SQL Server 2016, you can query on JSON column. See the documentation : Work with JSON data

The interesting part for you it's Analyze JSON data with SQL queries.

This done :

select Id, PostalCode
from Address
CROSS APPLY OPENJSON (Address.Data, N'$.Addresses')
WITH (
    TypeCode varchar(50) '$.TypeCode',
    PostalCode varchar(50) '$.PostalCode'
) AS AddressesJsonData
WHERE TypeCode  = N'PERMANENT'

---

Related Topics