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

  • Deleted
    • 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

  • Deleted
    • 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