Azure SQL - Handling JSON column in the table

  • mohankumar.trs

    SSC Veteran

    Points: 219

    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.

  • mohankumar.trs

    SSC Veteran

    Points: 219

    Can you help me if we have dynamic key value pairs and how to handle it

     

  • sgmunson

    SSC Guru

    Points: 110551

    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)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

  • Steve Collins

    Ten Centuries

    Points: 1080

    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;

    • This reply was modified 1 month, 2 weeks ago by  Steve Collins. Reason: made JSON nvarchar(max)

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • mohankumar.trs

    SSC Veteran

    Points: 219

    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)

     

  • Steve Collins

    Ten Centuries

    Points: 1080

    When the code posted above runs it produces the following output:

    Country	State	name	surname	age	gender
    US Alabama John Don 45 NULL
    Canada Toronto Mark Bagwell 35 Male

    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

  • sgmunson

    SSC Guru

    Points: 110551

    mohankumar.trs wrote:

    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)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply