In a SQL Server database, I have a table that contains 2 columns, ID and JSON. The JSON column contains JSON-formatted text. I have access to ISJSON, JSON_VALUE and JSON_QUERY, but not OPENJSON.
I am trying to write a query similar to the one below, that returns the ID column and all of the WorkstationID values. My query returns null. Can someone help me see what I am doing wrong?
Below is an example of the JSON data I am working with, as well as my query, and what I expect the output to look like. Thanks for any help that can be offered!
{
"areas":[
{
"type":"rect",
"coords":[
85,
17,
175,
98
],
"href":"#",
"alt":"0",
"title":"0",
"nameid":"592",
"WorkstationID":"592"
},
{
"type":"rect",
"coords":[
214,
23,
316,
97
],
"href":"#",
"alt":"0",
"title":"0",
"nameid":"594",
"WorkstationID":"594"
},
{
"type":"rect",
"coords":[
208,
132,
295,
183
],
"href":"#",
"alt":"0",
"title":"0",
"nameid":"595",
"WorkstationID":"595"
},
{
"type":"rect",
"coords":[
84,
118,
179,
248
],
"href":"#",
"alt":"0",
"title":"0",
"nameid":"596",
"WorkstationID":"596"
},
{
"type":"rect",
"coords":[
83,
264,
185,
322
],
"href":"#",
"alt":"0",
"title":"0",
"nameid":"597",
"WorkstationID":"597"
}
],
"img":"/filepath/filename"
}
SELECT ID, JSON_QUERY(JSON,'$.areas') AS WorkstationID FROM MyTable WHERE ID = 1
1,592 1,594 1,595 1,596 1,597
UPDATE: As was discussed in the comments below, OPENJSON seems to be the easy way to do this, but this is not possible for me because my compatibility level is < 130. I have found that the following regex string can be used to extract all WorkstationID’s from the JSON.
WorkstationID”:”([0-9]{1,4})
Could using a regular expression like this be useful to return the list of all WorkstationID’s contained in the JSON in a table format like what is explained in a table format?
Advertisement
Answer
Given this is a one-off for you, I’ve written an inefficient JSON to XML converter for you to access your data.
DECLARE @json varchar(8000) =
'{
"areas":[
{
"type":"rect",
"coords":[85, 17, 175, 98],
"href":"#",
"alt":"0",
"title":"0",
"nameid":"592",
"WorkstationID":"592"
},
{
"type":"rect",
"coords":[214, 23, 316, 97],
"href":"#",
"alt":"0",
"title":"0",
"nameid":"594",
"WorkstationID":"594"
},
{
"type":"rect",
"coords":[208, 132, 295, 183],
"href":"#",
"alt":"0",
"title":"0",
"nameid":"595",
"WorkstationID":"595"
},
{
"type":"rect",
"coords":[84, 118, 179, 248],
"href":"#",
"alt":"0",
"title":"0",
"nameid":"596",
"WorkstationID":"596"
},
{
"type":"rect",
"coords":[83, 264, 185, 322],
"href":"#",
"alt":"0",
"title":"0",
"nameid":"597",
"WorkstationID":"597"
}
],
"img":"/filepath/filename"
}';
DECLARE @Fields table ( field varchar(50), tag varchar(50), term varchar(1), id int IDENTITY(1,1) );
INSERT INTO @Fields VALUES
( '"type":"', 'type', '"' ),
( '"coords":[', 'coords', ']' ),
( '"href":"', 'href', '"' ),
( '"alt":"', 'alt', '"' ),
( '"title":"', 'title', '"' ),
( '"nameid":"', 'nameid', '"' ),
( '"WorkstationID":"', 'WorkstationID', '"' );
-- Convert JSON to XML --
DECLARE @id int = 1, @field varchar(50), @tag varchar(50), @term varchar(1);
WHILE @id <= ( SELECT MAX ( id ) FROM @Fields )
BEGIN
-- Current Field --
SELECT
@field = field,
@tag = tag,
@term = term
FROM @Fields WHERE id = @id;
-- Convert field to XML --
DECLARE @i int = CHARINDEX ( @field, @json );
WHILE @i > 0
BEGIN
-- Stuff in the opening tag --
SELECT @json = STUFF (
@json,
CHARINDEX ( @field, @json, @i ),
LEN ( @field ),
'<' + @tag + '>'
),
@json = STUFF (
@json,
CHARINDEX ( @term, @json, CHARINDEX ( @tag, @json, @i ) + 1 ),
LEN ( @term ),
'</' + @tag + '>'
);
-- Continue field search --
SET @i = CHARINDEX ( @field, @json, @i );
END
-- Next Field --
SET @id = @id + 1;
END
-- Final Cleanup --
SELECT
@json = REPLACE ( @json, '{', '<data>' ),
@json = REPLACE ( @json, '}', '</data>' ),
@json = REPLACE ( @json, '"areas":[', '<areas>' ),
@json = REPLACE ( @json, ']', '</areas>' ),
@json = REPLACE ( @json, '"img":"/filepath/filename"', '<img>/filepath/filename</img>' ),
@json = REPLACE ( @json, '>,', '>' );
-- Select resultset --
SELECT
t.f.value( 'type[1]', 'varchar(255)' ) AS [type],
t.f.value( 'coords[1]', 'varchar(255)' ) AS [coords],
t.f.value( 'href[1]', 'varchar(255)' ) AS [href],
t.f.value( 'alt[1]', 'varchar(255)' ) AS [alt],
t.f.value( 'title[1]', 'varchar(255)' ) AS [title],
t.f.value( 'nameid[1]', 'varchar(255)' ) AS [nameid],
t.f.value( 'WorkstationID[1]', 'varchar(255)' ) AS [WorkstationID],
t.f.value( '../../img[1]', 'varchar(255)' ) AS [img]
FROM (
SELECT CAST ( @json AS xml ) AS d
) x
CROSS APPLY x.d.nodes('//data/areas/data') t(f);
Returns
+------+--------------------+------+-----+-------+--------+---------------+--------------------+ | type | coords | href | alt | title | nameid | WorkstationID | img | +------+--------------------+------+-----+-------+--------+---------------+--------------------+ | rect | 85, 17, 175, 98 | # | 0 | 0 | 592 | 592 | /filepath/filename | | rect | 214, 23, 316, 97 | # | 0 | 0 | 594 | 594 | /filepath/filename | | rect | 208, 132, 295, 183 | # | 0 | 0 | 595 | 595 | /filepath/filename | | rect | 84, 118, 179, 248 | # | 0 | 0 | 596 | 596 | /filepath/filename | | rect | 83, 264, 185, 322 | # | 0 | 0 | 597 | 597 | /filepath/filename | +------+--------------------+------+-----+-------+--------+---------------+--------------------+
Your JSON gets converted to the following XML:
<data>
<areas>
<data>
<type>rect</type>
<coords>85, 17, 175, 98</coords>
<href>#</href>
<alt>0</alt>
<title>0</title>
<nameid>592</nameid>
<WorkstationID>592</WorkstationID>
</data>
<data>
<type>rect</type>
<coords>214, 23, 316, 97</coords>
<href>#</href>
<alt>0</alt>
<title>0</title>
<nameid>594</nameid>
<WorkstationID>594</WorkstationID>
</data>
<data>
<type>rect</type>
<coords>208, 132, 295, 183</coords>
<href>#</href>
<alt>0</alt>
<title>0</title>
<nameid>595</nameid>
<WorkstationID>595</WorkstationID>
</data>
<data>
<type>rect</type>
<coords>84, 118, 179, 248</coords>
<href>#</href>
<alt>0</alt>
<title>0</title>
<nameid>596</nameid>
<WorkstationID>596</WorkstationID>
</data>
<data>
<type>rect</type>
<coords>83, 264, 185, 322</coords>
<href>#</href>
<alt>0</alt>
<title>0</title>
<nameid>597</nameid>
<WorkstationID>597</WorkstationID>
</data>
</areas>
<img>/filepath/filename</img>
</data>