Sql-server – Querying JSON data

querysql serversql-server-2016

I am struggling to extract data from a table, which was partially inserted from a JSON file. The table has two fields (name nvarchar(50) and details nvarchar(max). The values of the fields are:

name      details
user01    [{"data": ["SQL Server", "DBA"], "id": 123456}, {"data": ["Profiler", "Tuning"], "recno": 123123}]
user02    [{"data": [], "recno": 121212}, {"data": ["MySQL"], "recno": 112233}]

I need to have this data in three columns (name, id and data) in a new table.

name,   id,     data
user01  123456  SQL Server, DBA 
user01  123123  Profiler, Tuning
user02  112233  MySQL

Would you know how to do it?

Best Answer

I've set up a table with your sample data with a couple of corrections:

  • changed "date" to "data" (for consistency with other JSON entries)
  • changed "recno" to "id" (for consistency with desired output and across JSON entries

Let me know if those corrections were invalid. It's possible to workaround / coalesce the values of "recno" and "id" if they are both used interchangeably in the real data.

USE [tempdb];
GO

CREATE TABLE #JsonStuff
(
    [name] varchar(10) NOT NULL,
    details nvarchar(max) NOT NULL
);
GO

INSERT INTO #JsonStuff
    ([name], details)
VALUES
    ('user01', N'[{"data": ["SQL Server", "DBA"], "id": 123456}, {"data": ["Profiler", "Tuning"], "id": 123123}]'),
    ('user02', N'[{"data": [], "id": 121212}, {"data": ["MySQL"], "id": 112233}]');

You can get close to your desired results with this query:

SELECT 
    [name], 
    main.id, 
    main.[data]
FROM #JsonStuff js
CROSS APPLY OPENJSON (js.details)
WITH
(
    id int '$.id',
    [data] nvarchar(max) AS JSON
) AS main
WHERE EXISTS 
(
    SELECT 1/0 
    FROM OPENJSON (main.[data])
);

The query selects each [name] from the table.

The OPENJSON function is used to access the structured data inside the documents.

It then uses CROSS APPLY over the JSON data in order to get a row per array entry in the JSON document.

Finally, the WHERE EXISTS clause is used to eliminate rows where the "data" array in the JSON document is empty. Results look like this:

screenshot of query results

To get rid of the square braces and double quotes, one would need to use OPENJSON again on the data element to get a row per inner array element, and then do normal string concatenation after that. Something like this:

SELECT 
    [name], 
    main.id, 
    STUFF
    (
        (
            SELECT ',' + [sublist].[value]
            FROM OPENJSON(main.[data]) AS sublist 
            FOR XML PATH ('')
        ), 1, 1, ''
    ) AS [data]
FROM #JsonStuff js
CROSS APPLY OPENJSON (js.details)
WITH
(
    id int '$.id',
    [data] nvarchar(max) AS JSON
) AS main
WHERE EXISTS 
(
    SELECT 1/0 
    FROM OPENJSON (main.[data])
);

Results look like this:

screenshot of results with square braces and double quotes removed

I should note that this is probably not great for performance (OPENJSON gets called a bunch of times, and the string concatenation is just the first one I came across).

Related Question