1

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?

5
  • 1
    Is this your actual JSON data? The JSON value [MySQL] is invalid; it should be ["MySQL"].
    – Dan Guzman
    Feb 18 '20 at 12:55
  • True. I corrected this
    – user110366
    Feb 18 '20 at 13:28
  • Please, tag your SQL Server version.
    – McNets
    Feb 18 '20 at 14:29
  • Is it {"date": ["Profiler", "Tuning"] or {"data": ["Profiler", "Tuning"] with a a
    – McNets
    Feb 18 '20 at 14:44
  • Yes, data updated
    – user110366
    Feb 18 '20 at 15:28
1

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).

2
  • 1
    Awesome! That works perfectly Josh. Thanks a mil
    – user110366
    Feb 18 '20 at 16:10
  • @xac Glad I could help! Feb 18 '20 at 16:21
-1

I don't know what the tables and their contents look at the moment, but as far as I understand from your question, I would recommend you shoud create a new column and then do something like

update tablename
set your_new_column = (select COALESCE (column1_info, column2_info) from tablename where id = your_rows_id)

where column1_info and column2_info is the information you would like extract from that column

see https://docs.microsoft.com/de-de/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.