OPENJSON - How to extract value from JSON object saved as NVARCHAR in SQL Server

Question

There is a column RawData of type NVARCHAR which contains JSON object as strings

RawData 
-------------------------------------------------------
{"ID":1,--other key/value(s)--,"object":{--object1--}}
{"ID":2,--other key/value(s)--,"object":{--object2--}}
{"ID":3,--other key/value(s)--,"object":{--object3--}}
{"ID":4,--other key/value(s)--,"object":{--object4--}}
{"ID":5,--other key/value(s)--,"object":{--object5--}}

This JSON string is big (1kb) and currently the most used part of this json is object(200 bytes).

i want to extract object part of these json strings by using OPENJSON.and i was not able to achieve a solution but i think there is a solution.

The result that i want is:

RawData 
----------------
{--object1--}
{--object2--}
{--object3--}
{--object4--}
{--object5--}

My attempts so far

SELECT * 
FROM OPENJSON((SELECT RawData From DATA_TB FOR JSON PATH))

Please add your attempt and why it is not working for you (unexpected result, error message, ...). Here you can find the documentation with many examples. — Sander
– Sander, Commented Sep 14, 2020 at 13:09
@Mr.AF that's not what you did. You created a new JSON string combining all results with FOR JSON PATH, then tried to parse it (and get the original data back) with OPENJSON. If you want to extract the value stored in someKey you can use SELECT JSON_QUERY(RawData,"$.someKey") from thatTable or something similar. For scalar values you can use JSON_VALUE — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 14, 2020 at 13:18
Nobody is. I used to be a SQL Server MVP and yet I always have to search for functions and names. In fact, my first comment mentioned JSON_VALUE because I forgot it only works with scalar values. OPENJSON is meant to parse complex JSON strings and return their contents as a table. You'll find the functions to query and modify JSON data in Validate, Query, and Change JSON Data with Built-in Functions — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 14, 2020 at 13:33
BTW I expect I'll forget that distinction by tomorrow. The more experienced one is, the more one uses docs, help and man pages. There's no way to remember every HTML tag, CSS rule, Javascript framework, ASP.NET technology and SQL Server feature and function. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 14, 2020 at 13:37

Sander · Accepted Answer · 2020-09-14 13:18:59Z

4

Looks like this should work for you.

Sample data

create table data_tb
(
  RawData nvarchar(max)
);

insert into data_tb (RawData) values
('{"ID":1, "key": "value1", "object":{ "name": "alfred" }}'),
('{"ID":2, "key": "value2", "object":{ "name": "bert" }}'),
('{"ID":3, "key": "value3", "object":{ "name": "cecil" }}'),
('{"ID":4, "key": "value4", "object":{ "name": "dominique" }}'),
('{"ID":5, "key": "value5", "object":{ "name": "elise" }}');

Solution

select d.RawData, json_query(d.RawData, '$.object') as Object
from data_tb d;

See it in action: fiddle.

answered Sep 14, 2020 at 13:18

Sander

4,0822 gold badges21 silver badges23 bronze badges

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

Comments

A Farmanbar · Accepted Answer · 2020-09-14 13:21:07Z

2

Something like this

 SELECT object 
 FROM DATA_TB as dt
 CROSS APPLY
 OPENJSON(dt.RawData) with (object     nvarchar(max) as json);

edited Sep 14, 2020 at 13:21

A Farmanbar

4,9285 gold badges32 silver badges61 bronze badges

answered Sep 14, 2020 at 13:16

SteveC

6,0252 gold badges14 silver badges26 bronze badges

Collectives™ on Stack Overflow

OPENJSON - How to extract value from JSON object saved as NVARCHAR in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related