Extract data from nested array for multiple columns

Question

I am using SQL Server 2016 and want to extract data from array.

But I got stuck.

DECLARE @idvalue NVARCHAR(MAX)='[{"testId":"b29b2327-527c-456d-8346-6bd22d198f21","testValue":"FAILURE","test":"b29b2327-527c-456d-8346-6bd22d198f21:FAILURE","testStartTimestamp":"2020-06-25T09:22:17.299Z"}
,{"testId":"4674bc9c-9551-496b-b488-8e138a4dc459","testValue":"FAILURE","test":"4674bc9c-9551-496b-b488-8e138a4dc459:FAILURE","testStartTimestamp":"2020-06-25T09:22:17.299Z"},{"testId":"38c20ac5-dbb7-43ad-b139-f8fde13d0ea5","testValue":"FAILURE","test":"38c20ac5-dbb7-43ad-b139-f8fde13d0ea5:FAILURE","testStartTimestamp":"2020-06-25T09:22:17.3Z"},{"testId":"4a3b3102-d3fa-4c2c-b3cf-46e6822fae5c","testValue":"FAILURE","test":"4a3b3102-d3fa-4c2c-b3cf-46e6822fae5c:FAILURE","testStartTimestamp":"2020-06-25T09:22:17.3Z"},{"testId":"f80bec6d-ab5c-4f63-8aea-cd2f5179195e","testValue":"FAILURE","test":"f80bec6d-ab5c-4f63-8aea-cd2f5179195e:FAILURE","testStartTimestamp":"2020-06-25T09:22:17.3Z"},{"testId":"e75896d2-f314-4423-87be-ea70b2ba5adb","testValue":"FAILURE","test":"e75896d2-f314-4423-87be-ea70b2ba5adb:FAILURE","testStartTimestamp":"2020-06-25T09:22:17.301Z"}]';

select P_conversations_conversationId,
       P_conversations_participants_sessions_flow_outcomes,
       JSON_VALUE(REPLACE(P_conversations_participants_sessions_flow_outcomes,'[',''),'$.testId') as testId,
       JSON_VALUE(REPLACE(REPLACE(P_conversations_participants_sessions_flow_outcomes,'[',''),']',''),'$.testEndTimestamp') as testEndTimestamp,
       JSON_VALUE(REPLACE(@idvalue,'[',''),'$.testValue') AS [testValue],
       JSON_VALUE(REPLACE(@idvalue,'[',''),'$.testStartTimestamp') as testStartTimestamp,
       JSON_VALUE(REPLACE(@idvalue,'[',''),'$.test') as test 
FROM Dashboardtable;

In the declare statement I have given sample data.

So what's wrong with your SQL? Where did you get stuck? We can't run the above, as we don't have access to your instance, and you haven't given us DDL and DML for your table dbo.Dashboardtable. — Thom A
– Thom A ♦, Commented Dec 22, 2020 at 9:09
Thanks for your quick reply. Msg 13609, Level 16, State 1, Line 8 JSON text is not properly formatted. Unexpected character ',' is found at position 201. — kathija
– kathija, Commented Dec 22, 2020 at 9:12
This is what I have encountered when I am trying to execute the sql. — kathija
– kathija, Commented Dec 22, 2020 at 9:13
An approach, based on OPENJSON and explicit schema, is probbaly your first option. — Zhorov
– Zhorov, Commented Dec 22, 2020 at 9:13
I have tried with OPENJSON. I am getting NULL values for the datetime columns — kathija
– kathija, Commented Dec 22, 2020 at 9:17

Dale K · Accepted Answer · 2020-12-22 09:23:14Z

1

Assuming you just want the data out of the variable, then you can use OPENJSON. If not, then you need to tell us what you're actually after.

SELECT *
FROM OPENJSON(@idvalue)
     WITH(testId uniqueidentifier,
          testValue varchar(10),
          test uniqueidentifier,
          testStartTimestamp datetimeoffset(1));

edited Dec 22, 2020 at 9:23

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Dec 22, 2020 at 9:17

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kathija Over a year ago

Sorry for posting wrong data.It has been updated. There we have enddatetime also and hat is coming as NULL. I tried with the above query as well. Still this also giving NULL for enddatetime column

Thom A Over a year ago

There is not endatetime (or I assume you mean testEndTimestamp) node in your data, @kathija , so of course it'll return NULL.

Zhorov · Accepted Answer · 2020-12-22 12:05:31Z

1

Yoy may try to parse the input JSON with OPENJSON() and explicit schema:

 SELECT *
 FROM OPENJSON(@idvalue) WITH (
    testId varchar(36) '$.testId',
    testValue varchar(100) '$.testValue',
    test varchar(100) '$.test',
    testStartTimestamp datetime2(3) '$.testStartTimestamp'
 )

Result:

testId                                  testValue   test                                             testStartTimestamp
b29b2327-527c-456d-8346-6bd22d198f21    FAILURE     b29b2327-527c-456d-8346-6bd22d198f21:FAILURE    2020-06-25 09:22:17.299
4674bc9c-9551-496b-b488-8e138a4dc459    FAILURE     4674bc9c-9551-496b-b488-8e138a4dc459:FAILURE    2020-06-25 09:22:17.299
38c20ac5-dbb7-43ad-b139-f8fde13d0ea5    FAILURE     38c20ac5-dbb7-43ad-b139-f8fde13d0ea5:FAILURE    2020-06-25 09:22:17.300
4a3b3102-d3fa-4c2c-b3cf-46e6822fae5c    FAILURE     4a3b3102-d3fa-4c2c-b3cf-46e6822fae5c:FAILURE    2020-06-25 09:22:17.300
f80bec6d-ab5c-4f63-8aea-cd2f5179195e    FAILURE     f80bec6d-ab5c-4f63-8aea-cd2f5179195e:FAILURE    2020-06-25 09:22:17.300
e75896d2-f314-4423-87be-ea70b2ba5adb    FAILURE     e75896d2-f314-4423-87be-ea70b2ba5adb:FAILURE    2020-06-25 09:22:17.301

If the JSON content is stored in a table, you need an additional APPLY operator:

SELECT j.*
-- FROM Dashboardtable d
FROM (VALUES (@idvalue)) d (P_conversations_participants_sessions_flow_outcomes)
OUTER APPLY OPENJSON(d.P_conversations_participants_sessions_flow_outcomes) WITH (
   testId varchar(36) '$.testId',
   testValue varchar(100) '$.testValue',
   test varchar(100) '$.test',
   testStartTimestamp datetime2(3) '$.testStartTimestamp'
) j

As an additional option, if you want to extract scalar values from JSON array, you need to use an index in the path definition:

SELECT
   JSON_VALUE(@idvalue, '$[0].testValue') AS testValue,
   JSON_VALUE(@idvalue, '$[0].testStartTimestamp') AS testStartTimestamp,
   JSON_VALUE(@idvalue, '$[0].test') AS test

edited Dec 22, 2020 at 12:05

answered Dec 22, 2020 at 9:16

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

2 Comments

kathija Over a year ago

Thanks for your reply. index path will not be static. It can be changed dynamically based on data. It can be 1, 2 and may be more also.

Zhorov Over a year ago

@kathija OK. In this case usisng OPENJSON to parse all JSON items should be your first option.

Collectives™ on Stack Overflow

Extract data from nested array for multiple columns

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related