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](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql) you can find the documentation with many examples. — Sander, Sep 14 '20 at 13:09
Any proof of personal attempt is appreciated and can be build upon. — Sander, Sep 14 '20 at 13:11
Why are you converting the field to JSON only to parse it back? Why not use eg `JSON_VALUE(RawData ...)` to get the actual data you want? *What* data do you want in the first place? Post *actual* sample data and the desired results — Panagiotis Kanavos, Sep 14 '20 at 13:14
@PanagiotisKanavos i have done what this linked said https://learn.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server?view=sql-server-ver15 — A Farmanbar, Sep 14 '20 at 13:15
@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, Sep 14 '20 at 13:18
@PanagiotisKanavos actually i am not expert on OPENJSON thanks for your nice comment. — A Farmanbar, Sep 14 '20 at 13:26
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](https://learn.microsoft.com/en-us/sql/relational-databases/json/validate-query-and-change-json-data-with-built-in-functions-sql-server?view=sql-server-ver15) — Panagiotis Kanavos, Sep 14 '20 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, Sep 14 '20 at 13:37

score 4 · Accepted Answer · answered Sep 14 '20 at 13:18

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.

score 2 · Answer 2 · edited Sep 14 '20 at 13:21

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 '20 at 13:21

A Farmanbar

4,381
5
24
42

answered Sep 14 '20 at 13:16

SteveC

5,955
2
11
24

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

2 Answers2

Linked