August 5, 2020 at 11:11 am
Hi
I want to convert table content which contain the json values in the columnin Azure SQL server. like below
Country state json_value
US Alabama {"name':"John","surname":"Don","age":45,}
Canada Toronto {"name":"Mark","surname":"Bagwell","age":35,"Gender":"Male"}
I need to convert the table details in query itself like below
country state name surname age Gender
US Alabama John Don 45 NULL
Canada Toronto Mark Bagwell 35 Male
Kindly help me in achieve is using openjson function or other function in SQL query.
August 5, 2020 at 2:30 pm
Can you help me if we have dynamic key value pairs and how to handle it
August 5, 2020 at 6:24 pm
Dynamic key value pairs would require a dynamic SQL pivot. However, take a look at the following code, which assumes your JSON values are always separated by commas, and that a name / value pair is always separated by a colon (:). This code makes use of a string splitter function DelimitedSplit8K, for which the code can be found on this site under Articles, and search for "Tally Oh", by Jeff Moden. The code below uses that function to simply split your JSON_Value column into its component parts and won't have the string overhead of JSON format handling. My Dell laptop with a mere Core i3 processor does the split on the two example records instantaneously. Here's the code:
CREATE TABLE dbo.AZURE_JSON (
RowID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
Country varchar(10),
[State] varchar(10),
[JSON_Value] varchar(50)
);
INSERT INTO dbo.AZURE_JSON (Country, [State], [JSON_Value])
VALUES('US', 'Alabama', '{"name":"John","surname":"Don","age":45}'),
('Canada', 'Toronto', '{"name":"Mark","surname":"Bagwell","age":35}');
WITH ALL_DATA AS (
SELECT
AJ.RowID,
S.ItemNumber,
AJ.Country,
AJ.[State],
MAX(
CASE S2.ItemNumber
WHEN 1 THEN S2.Item
ELSE NULL
END
) AS ColumnName,
MAX(
CASE S2.ItemNumber
WHEN 2 THEN S2.Item
ELSE NULL
END
) AS Element
FROM dbo.AZURE_JSON AS AJ
CROSS APPLY dbo.DelimitedSplit8K(REPLACE(REPLACE(REPLACE(AJ.[JSON_Value], '"', ''), '{', ''), '}', ''), ',') AS S
CROSS APPLY dbo.DelimitedSplit8K(S.Item, ':') AS S2
GROUP BY
AJ.RowID,
S.ItemNumber,
AJ.Country,
AJ.[State]
)
SELECT
RowID,
Country,
[State],
MAX([name]) AS [name],
MAX([surname]) AS surname,
MAX([age]) AS age
FROM ALL_DATA AS AD
PIVOT (MAX(Element) FOR ColumnName IN ([name], [surname], [age])) AS P
GROUP BY
RowID,
Country,
[State];
DROP TABLE dbo.AZURE_JSON;
GO
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 5, 2020 at 7:13 pm
drop table if exists dbo.TestAZURE_JSON;
go
CREATE TABLE dbo.TestAZURE_JSON (
RowID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
Country varchar(10),
[State] varchar(10),
[JSON_Value] nvarchar(max));
go
INSERT dbo.TestAZURE_JSON(Country, [State], [JSON_Value]) VALUES
('US', 'Alabama', '{"name":"John","surname":"Don","age":45}'),
('Canada', 'Toronto', '{"name":"Mark","surname":"Bagwell","age":35,"gender":"Male"}');
select
aj.Country, aj.[State], j_open.*
from
dbo.TestAZURE_JSON aj
cross apply
openjson(aj.[JSON_Value]) with
([name] varchar(20),
surname varchar(20),
age int,
gender varchar(10)) j_open;
/* drop test table */
drop table dbo.TestAZURE_JSON;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 6, 2020 at 10:33 am
Thanks for your help. I have got the below response which works good to me. But , I am getting [] in columns with the values. Is there anyway we remove it in the output.
create table jsontable(country varchar(100), state varchar(100),jsonvalue varchar(500));
insert into jsontable
select 'US','Alabama','{"name:"John","surname":"Doe","age":45}'
union
select 'Canada','Toronto','{"name":"Mark","surname":"Bagwell","age":35,"gender":"Male"}
union
select 'Australia',' Sydney','{"name":"Steve","surname":"Irvin","gender":'Male"}'
Declare @SQL varchar(max) = stuff((Select ','+QuoteName([Key]) FROM (SELECT DISTINCT(j.[Key]) FROM jsontable T CROSS APPLY OPENJSON(T.jsonvalue) AS j) A order by 1 for XML Path('')),1,1,'');
set @SQL = ' SELECT * FROM (SELECT T.country,T.state,j.[Key],j.[Value] FROM jsontable T CROSS APPLY OPENJSON(T.jsonvalue) AS j ) src
pivot(max(value) from in ('+ @SQL+')) pvt
Exec(@SQL)
August 6, 2020 at 11:52 am
When the code posted above runs it produces the following output:
CountryStatenamesurnameagegender
USAlabamaJohnDon45NULL
CanadaTorontoMarkBagwell35Male
This is exactly what was requested no? Is it a requirement the query be dynamic because the schema of the JSON is unknown?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 6, 2020 at 2:30 pm
Thanks for your help. I have got the below response which works good to me. But , I am getting [] in columns with the values. Is there anyway we remove it in the output.
create table jsontable(country varchar(100), state varchar(100),jsonvalue varchar(500));
insert into jsontable
select 'US','Alabama','{"name:"John","surname":"Doe","age":45}'
union
select 'Canada','Toronto','{"name":"Mark","surname":"Bagwell","age":35,"gender":"Male"}
union
select 'Australia',' Sydney','{"name":"Steve","surname":"Irvin","gender":'Male"}'
Declare @SQL varchar(max) = stuff((Select ','+QuoteName([Key]) FROM (SELECT DISTINCT(j.[Key]) FROM jsontable T CROSS APPLY OPENJSON(T.jsonvalue) AS j) A order by 1 for XML Path('')),1,1,'');
set @SQL = ' SELECT * FROM (SELECT T.country,T.state,j.[Key],j.[Value] FROM jsontable T CROSS APPLY OPENJSON(T.jsonvalue) AS j ) src
pivot(max(value) from in ('+ @SQL+')) pvt
Exec(@SQL)
And to add to Steve Collins' comments, how about the missing " at the end of the word name in the first select of the insert?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply