I'm using DataTables, DataTables Editor, JavaScript, and MSSQL 2016.
I'd like to parse this string in SQL Server:
{
"action":"edit",
"data": {
"2019-08-03":{
"Description":"sdfsafasdfasdf",
"FirstFrozenStep":"333"
}
}
}
I don't know how to access the key "2019-08-03". This represents the primary key, or the DT_RowId
in DataTables Editor. It's dynamic... It could change.
Historically, I have just manipulated the data in JavaScript to a FLAT object, which is WAY easier to parse in SQL Server:
{
"action":"edit",
"DT_RowId":"2019-08-03",
"Description":"sdfsafasdfasdf",
"FirstFrozenStep":"333"
}
HOWEVER, I would like to know how to use json_query
, json_value
, and openjson()
to drill down to the "dynamic" key mentioned above, and then access its values.
Here are all my FAILED attempts:
declare
@jsonRequest nvarchar(max) = '{"action":"edit","data":{"2019-08-03":{"Description":"sdfsafasdfasdf","FirstFrozenStep":"333"}}}'
,@json2 nvarchar(max) = '{"2019-08-03":{"Description":"sdfsafasdfasdf","FirstFrozenStep":"333"}}'
,@jsonEASY nvarchar(max) = '{"action":"edit","DT_RowId":"2019-08-03","Description":"sdfsafasdfasdf","FirstFrozenStep":"333"}'
select
json_value(@jsonRequest, '$.action') as [action]
--,json_value(@jsonRequest, '$.data.[0]') as [action]
--,json_query(@jsonRequest, '$.data[0]')
--,json_query(@jsonRequest, '$.data.[0]')
--,json_query(@jsonRequest, '$.data[0].Description')
--,json_query(@jsonRequest, '$.data.Description')
--,json_query(@jsonRequest, '$.data.[0].Description')
select
[Key]
,Value
,Type
--,json_query(value, '$')
from
openjson(@jsonRequest)
SELECT x.[Key], x.[Value]
FROM OPENJSON(@jsonRequest, '$') AS x;
select
x.[Key]
,x.[Value]
--,json_query(x.value, '$')
--,(select * from openjson(x.value))
FROM OPENJSON(@jsonRequest, '$') AS x;
SELECT x.[Key], x.[Value]
FROM OPENJSON(@json2, '$') AS x;
select
json_value(@jsonEASY, '$.action') as [action]
,json_value(@jsonEASY, '$.DT_RowId') as [DT_RowId]
,json_value(@jsonEASY, '$.Description') as [Description]
The most explicit and type-safe approach might be this:
I define your JSON with two dynamic keys
DECLARE @json NVARCHAR(MAX)=
N'{
"action":"edit",
"data": {
"2019-08-03":{
"Description":"sdfsafasdfasdf",
"FirstFrozenStep":"333"
},
"2019-08-04":{
"Description":"blah4",
"FirstFrozenStep":"444"
}
}
}';
--the query
SELECT A.[action]
,B.[key]
,C.*
FROM OPENJSON(@json) WITH([action] NVARCHAR(100)
,[data] NVARCHAR(MAX) AS JSON) A
OUTER APPLY OPENJSON(A.[data]) B
OUTER APPLY OPENJSON(B.[value]) WITH([Description] NVARCHAR(100)
,FirstFrozenStep INT) C;
The result
action key Description FirstFrozenStep
edit 2019-08-03 sdfsafasdfasdf 333
edit 2019-08-04 blah4 444
The idea in short:
OPENJSON()
will return the two first-level-keys under the alias A
. The data
element is returned AS JSON
, which allows to proceed with this later.OPENJSON()
gets A.[data]
as input and is needed to get hands on the key
, which is your date.OPENJSON()
now gets B.[value]
as input.The WITH
-clause allows to read the inner elements implicitly pivoted and typed.
In general: In generic data containers it is no good idea to use descriptive parts as content. This is possible and might look clever, but it was much better to place your date as content within a date
-key.
I'm getting this message: "Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon."
@raydlevel5 But not because of With within an OPENJSON statement. This must be something else...
SORRY, I executed when attached on a DB that had the wrong "version"... will report back. Thank you.
I'm accepting your answer because it works, and you've explained a tool that I can use in the future (outer apply). I totally agree about your statement against using generic keys; however, the request you're seeing is "raw" and built by DataTables Editor. Since I have the option to manipulate the request before it hits SQL, I will probably continue "flattening" the data to make it easier to parse.... BUT you've provided a way for us to gain access to generic keys!!!
@raydlevel5 Just one more reason against content-keys: They are nvarchar in any case. Normal content is in a way typed (e.g. by the way a date is formatted) and you can read it with WITH in a type-safe way.