none
Jason Result transformation RRS feed

  • Question

  • Hi, how can I achieve below result set?

    DECLARE @Table table (ID int,String varchar(100))

    insert into @Table

    values (1,'{"A":"1", "B":"2", "C":"3"}'),(2,'{"A":"11", "B":"22", "C":"33"}')


    select * from @Table

    Result Set:

    ID A B C

    1 1 2 3

    2 11 22 33


    Royal Thomas



    Friday, August 9, 2019 10:24 PM

Answers


  • values (1,'{"A":"1", "B":"2", "C":"3"}'),
             
    (2,'{"A":"11", "B":"22", "C":"33"}, {"A":"111", "B":"222", "C":"333"}')

    Is the JSON document formatted correctly?

    (2,'[ {"A":"11", "B":"22", "C":"33"}, {"A":"111", "B":"222", "C":"333"} ]')

    --- 

    What is the version of SQL Server?

    If your version of SQL Server is 2016 (or later) then try the following suggestion:

    -- code #1
    SELECT T.ID, J.A, J.B, J.C
      from @Table as T
           cross apply openjson (T.String) 
                               with (A smallint '$.A',
                                     B smallint '$.B',
                                     C smallint '$.C') as J;

    ---

    But if your version of SQL Server is earlier than 2016, you might be able to use either the parseJSON function or the fnSplitJson2 function.

     

    If this answer was helpful, remember to mark it.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Edited by José Diz Saturday, August 10, 2019 3:11 PM
    • Marked as answer by Royal Thomas Wednesday, August 14, 2019 6:34 PM
    Friday, August 9, 2019 10:56 PM
  • Hi Royal Thomas,

     

    Also please try following script.

     

    For more information , please refer to JSON_VALUE (Transact-SQL) and Parse and Transform JSON Data with OPENJSON (SQL Server).

     
    DECLARE @Table table (ID int,String varchar(100))
    insert into @Table
    values (1,'{"A":"1", "B":"2", "C":"3"}'),(2,'{"A":"11", "B":"22", "C":"33"}')
    
    SELECT id,
    JSON_VALUE(String,'$.A') A,
    JSON_VALUE(String,'$.B') B,
    JSON_VALUE(String,'$.C') C
    from @Table 
    /*
    ID          A      B      C
    ----------- ------ ------ ------
    1           1      2      3
    2           11     22     33
    */

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Royal Thomas Wednesday, August 14, 2019 6:34 PM
    Monday, August 12, 2019 6:28 AM

All replies


  • values (1,'{"A":"1", "B":"2", "C":"3"}'),
             
    (2,'{"A":"11", "B":"22", "C":"33"}, {"A":"111", "B":"222", "C":"333"}')

    Is the JSON document formatted correctly?

    (2,'[ {"A":"11", "B":"22", "C":"33"}, {"A":"111", "B":"222", "C":"333"} ]')

    --- 

    What is the version of SQL Server?

    If your version of SQL Server is 2016 (or later) then try the following suggestion:

    -- code #1
    SELECT T.ID, J.A, J.B, J.C
      from @Table as T
           cross apply openjson (T.String) 
                               with (A smallint '$.A',
                                     B smallint '$.B',
                                     C smallint '$.C') as J;

    ---

    But if your version of SQL Server is earlier than 2016, you might be able to use either the parseJSON function or the fnSplitJson2 function.

     

    If this answer was helpful, remember to mark it.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Edited by José Diz Saturday, August 10, 2019 3:11 PM
    • Marked as answer by Royal Thomas Wednesday, August 14, 2019 6:34 PM
    Friday, August 9, 2019 10:56 PM
  • Hi Royal Thomas,

     

    Also please try following script.

     

    For more information , please refer to JSON_VALUE (Transact-SQL) and Parse and Transform JSON Data with OPENJSON (SQL Server).

     
    DECLARE @Table table (ID int,String varchar(100))
    insert into @Table
    values (1,'{"A":"1", "B":"2", "C":"3"}'),(2,'{"A":"11", "B":"22", "C":"33"}')
    
    SELECT id,
    JSON_VALUE(String,'$.A') A,
    JSON_VALUE(String,'$.B') B,
    JSON_VALUE(String,'$.C') C
    from @Table 
    /*
    ID          A      B      C
    ----------- ------ ------ ------
    1           1      2      3
    2           11     22     33
    */

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Royal Thomas Wednesday, August 14, 2019 6:34 PM
    Monday, August 12, 2019 6:28 AM
  • Hi Royal Thomas,,

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 14, 2019 9:32 AM