June 1, 2018 at 12:43 pm
Hi Everyone,
Does anyone know or suggest a way to extract column information from the Json array. We are still using sql 2014 so jsonopen is not supported. I am trying to work through the same example as attached here.
Thanks.
June 1, 2018 at 12:54 pm
Papil - Friday, June 1, 2018 12:43 PMHi Everyone,Does anyone know or suggest a way to extract column information from the Json array. We are still using sql 2014 so jsonopen is not supported. I am trying to work through the same example as attached here.
Thanks.
Reading JSON is not trivial... If you could translate it to XML, then you'd have a much better shot at getting a relatively easy solution, but getting the functionality of JSONOPEN without SQL Server 2016 would be rather significantly difficult, and may well be beyond the scope of what kind of help you could get in ANY online forum.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 1, 2018 at 12:55 pm
Papil - Friday, June 1, 2018 12:43 PMHi Everyone,Does anyone know or suggest a way to extract column information from the Json array. We are still using sql 2014 so jsonopen is not supported. I am trying to work through the same example as attached here.
Thanks.
You may find something of interest here.
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 1, 2018 at 1:54 pm
i ran the function in the above link. I want to pass table into the function but it looks like i am doing it wrong-
Please suggest.
SELECT dbo.fn_parse_json2xml(select jsoncolumn from table A);
Thanks.
June 1, 2018 at 2:01 pm
Powershell can do it.
June 1, 2018 at 2:04 pm
Papil - Friday, June 1, 2018 1:54 PMi ran the function in the above link. I want to pass table into the function but it looks like i am doing it wrong-Please suggest.
SELECT dbo.fn_parse_json2xml(select jsoncolumn from table A);
Thanks.
You might need it to be:SELECT dbo.fn_parse_json2xml(X.jsoncolumn) AS XML_Column
FROM tableA AS X;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 1, 2018 at 2:12 pm
Thanks but i get blank column when i run above. Format of my column looks like below-
[{"Id":1725,"Number":"12345","quantity":1,"Block":true},{"Id":125,"Number":"1234544","quantity":1,"Block":true}]
June 2, 2018 at 3:19 pm
can anyone help with the same. thanks in advance.
June 4, 2018 at 11:00 am
Papil - Friday, June 1, 2018 2:12 PMThanks but i get blank column when i run above. Format of my column looks like below-[{"Id":1725,"Number":"12345","quantity":1,"Block":true},{"Id":125,"Number":"1234544","quantity":1,"Block":true}]
This code will do the job using that parseJSON function, but it's not dynamic, and is built on knowing the column names and the value types. That's why processing JSON is NOT trivial. That parser doesn't really do much but provide a framework for you to do a dynamic SQL pivot. I don't have time to go that far today, so here's what I did do:WITH RAW_DATA AS (
SELECT PJ.*
FROM dbo.ParseJSON('[{"Id":1725,"Number":"12345","quantity":1,"Block":true},{"Id":125,"Number":"1234544","quantity":1,"Block":true}]') AS PJ
WHERE PJ.ValueType IN ('boolean', 'int', 'string')
)
SELECT
RD.parent_ID,
MAX(CASE RD.NAME WHEN 'id' THEN CONVERT(int, RD.StringValue) ELSE NULL END) AS id,
MAX(CASE RD.NAME WHEN 'Number' THEN CONVERT(varchar(20), RD.StringValue) ELSE NULL END) AS Number,
MAX(CASE RD.NAME WHEN 'quantity' THEN CONVERT(int, RD.StringValue) ELSE NULL END) AS quantity,
CONVERT(bit, MAX(CASE RD.NAME WHEN 'Block' THEN CONVERT(tinyint, CONVERT(bit, RD.StringValue)) ELSE NULL END)) AS [Block]
FROM RAW_DATA AS RD
GROUP BY RD.parent_ID
ORDER BY RD.parent_ID;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply