CODEWITHSUNDEEP
sql-serveropen-json
A Farmanbar
A Farmanbar

Reputation: 4788

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

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

Upvotes: 0

Views: 3625

Answers (2)

SteveC
SteveC

Reputation: 6015

Something like this

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

Upvotes: 2

Sander
Sander

Reputation: 4042

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.

Upvotes: 4

Related Questions