Performance SQL - JSON extraction

Question

I need to extract an ID from some a string field (JSON code). From performance perspective what is the best way to obtain this ID and where?

Remarks:

there are different type of messages
payload: JSON field
sample of message type1: {"RequestId":"4B5E95D49D3E46548904083D0CD17521"}
sample of message type2:{"createdById":"XXXXXXXXX","requestId":"15F79EC78E5243A487337C0FE61A9E00","sessionId":null}
SQL in order to extract the id:

select  
    (Case 
        when eventType = 'Type1' then substring (payload, CHARINDEX('{',payload)+ LEN('{"RequestId":"'),   CHARINDEX ('"}', payload) -( CHARINDEX('{',payload)+ LEN('{"RequestId":"'))) 
        when eventType = ' Type2' then substring (payload, CHARINDEX('"requestId":"',payload)+ LEN('"requestId":"'),   CHARINDEX ('","sessionId"', payload) -(  CHARINDEX('"requestId":"',payload)+ LEN('"requestId":"'))) 
        else 'none' END) as RequestID 
  from table

Why use CHARINDEX and SUBSTRING not use SQL Server's JSON functionality? — Thom A
– Thom A ♦, Commented Jun 24, 2021 at 9:47
Thanks! Larnu...to tell you the truth , it is really the first time that I'm working in something like this, I'm truly not familiar with these capabilities — DeboraID
– DeboraID, Commented Jun 24, 2021 at 11:00

j.v. · Accepted Answer · 2021-06-24 10:17:19Z

1

If you are running SQL Server 2016+ then use this query:

select JSON_VALUE(t.payload, '$.requestId') from table t

answered Jun 24, 2021 at 10:17

j.v.

9976 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

Performance SQL - JSON extraction

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related