Hi all, been struggling with this all day and can't seem to parse out a nested value from a JSON string. I'm trying to get to the value of a "custom_data_field" where the "id" is 1 (to get value for "Company") in the following Query:
declare @returnval varchar(max)
set @returnval='
[{"id":349978,
"resource_uri":"https:\/\/api.mysampleapi.com\/rest\/v1.0\/vehicle\/349978",
"name":"Test Vehicle",
"code":"12345",
"status":1,
"taxable":"state",
"tank_capacity":0,
"product_type":"Allow All",
"product":[],
"custom_data_field":[{"id":1,"label":"Company","value":"2"},{"id":2,"label":null,"value":null},{"id":3,"label":null,"value":null}],
"access_groups":[],
"drivers":[]}]'
select a.[id], a.[name], a., a.[status], a.[taxable], a.[product_type], a.[custom_data_field.id] as [customdatafield] from OPENJSON(@returnval) with
(id int '$.id', name varchar(150) '$.name', code varchar(20) '$.code', status tinyint '$.status', taxable varchar(20) '$.taxable', product_type varchar(20) '$.product_type', [custom_data_field.id] nvarchar(max) '$.custom_data_field.id' ) a
I'm able to pull the root values fine, but can't seem to rip out the "Company" value in the string. I can't seem to get my customdatafield column to return anything but null.
June 11, 2020 at 9:08 pm
It seems like my issue is with the extra set of brackets around the json text. However, this is a multiple row JSON string. So my FULL JSON looks like this:
[{"id":349978,"resource_uri":"https:\/\/api.myapi.com\/rest\/v1.0\/vehicle\/349978","name":"Test Vehicle",
"code":"12345","status":1,"taxable":"state","tank_capacity":0,"product_type":"Allow All","product":[],"custom_data_field":[{"id":1,"label":"Company","value":"2"},
{"id":2,"label":null,"value":null},{"id":3,"label":null,"value":null}],"access_groups":[],"drivers":[]},
{"id":349979,"resource_uri":"https:\/\/api.myapi.com\/rest\/v1.0\/vehicle\/349979","name":"TestVehicle_ONRD","code":"TES123",
"status":1,"taxable":"none","tank_capacity":0,"product_type":"Allow All","product":[],
"custom_data_field":[{"id":1,"label":"Company","value":"70"},{"id":2,"label":null,"value":null},{"id":3,"label":null,"value":null}],"access_groups":[],"drivers":[]}]
Does this help?
DECLARE @json NVARCHAR(MAX);
SET @json
= N'[{"id":349978,"resource_uri":"https:\/\/api.myapi.com\/rest\/v1.0\/vehicle\/349978","name":"Test Vehicle",
"code":"12345","status":1,"taxable":"state","tank_capacity":0,"product_type":"Allow All","product":[],"custom_data_field":[{"id":1,"label":"Company","value":"2"},
{"id":2,"label":null,"value":null},{"id":3,"label":null,"value":null}],"access_groups":[],"drivers":[]},
{"id":349979,"resource_uri":"https:\/\/api.myapi.com\/rest\/v1.0\/vehicle\/349979","name":"TestVehicle_ONRD","code":"TES123",
"status":1,"taxable":"none","tank_capacity":0,"product_type":"Allow All","product":[],
"custom_data_field":[{"id":1,"label":"Company","value":"70"},{"id":2,"label":null,"value":null},{"id":3,"label":null,"value":null}],"access_groups":[],"drivers":[]}]';
SELECT d.id, cd.value
FROM
OPENJSON(@json)
WITH
(
id INT 'strict $.id'
,custom_data_field NVARCHAR(MAX) AS JSON
) d
CROSS APPLY
OPENJSON(custom_data_field)
WITH
(
id INT
,value INT
) cd
WHERE cd.id = 1;
June 12, 2020 at 12:25 pm
Brilliant. Absolutely brilliant! Thank you so much, I'm new to working with JSON in SQL and just couldn't find the right technique. I was able to write a c# assembly to send curl commands to my vendor's API but struggled with ripping that JSON apart.
Thank you for your help!
June 12, 2020 at 1:17 pm
Brilliant. Absolutely brilliant! Thank you so much, I'm new to working with JSON in SQL and just couldn't find the right technique. I was able to write a c# assembly to send curl commands to my vendor's API but struggled with ripping that JSON apart.
Thank you for your help!
Glad to help. Thank you for posting back, it makes all the difference.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy