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))
FOR JSON PATH
, then tried to parse it (and get the original data back) with OPENJSON. If you want to extract the value stored insomeKey
you can useSELECT JSON_QUERY(RawData,"$.someKey") from thatTable
or something similar. For scalar values you can useJSON_VALUE
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