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;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply