select from .json

arkiboys 9,686 Reputation points
2022-05-09T12:58:08.94+00:00

How can I get the values for mparent.oDates.name and mparent.oDates.actual and mparent.oDates.estimated ?
Thank you

[
    {
        "dname": "mydname",
        "book": "book name",
        "mparent": [
            {
                "dname": "dvf",
                "state": "uk",
                "oDates": [
                    {
                        "name": "stays",
                        "estimated": "2019-03-31"
                    },
                    {
                        "name": "somename",
                        "estimated": "2022-09-17",
                        "actual": "2022-10-17"
                    },
            ...
        ]
        }
    ],
    ...
...
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

3 additional answers

Sort by: Most helpful
  1. Olaf Helper 42,926 Reputation points
    2022-05-09T13:20:11.497+00:00

    Which SQL Server version are you using?
    Since 2016 we have JSON Functions (Transact-SQL) you could use, see
    https://learn.microsoft.com/en-us/sql/t-sql/functions/json-functions-transact-sql?view=sql-server-ver15


  2. Bert Zhou-msft 3,421 Reputation points
    2022-05-10T06:09:21.263+00:00

    Hi,@arkiboys

    Welcome to Microsoft T-SQL Q&A Forum!

    If you only want to display fields, then this solution works. Please check this:

    DECLARE @JSON NVARCHAR(MAX)=   
    N'{  
      "dname": "mydname",  
      "book": "book name",  
      "mparent":  
     {  
      "dname" : "dvf",  
                 "state" : "uk",  
      "oDates":  
      [  
     {  
                       "name" : "stays",  
                       "estimated": "2019-03-31"  
                     },  
      {  
                         "name": "somename",  
                         "estimated": "2022-09-17",  
                         "actual": "2022-10-17"  
                     }  
      ]  
     }  
        
    }'  
    SELECT JSON_VALUE(@JSON, '$.mparent.oDates[0].name') AS dname1,  
    JSON_VALUE(@JSON, '$.mparent.oDates[0].estimated') AS estimated1,  
    JSON_VALUE(@JSON, '$.mparent.oDates[1].name') AS dname2,  
    JSON_VALUE(@JSON, '$.mparent.oDates[1].estimated') AS estimated2,  
    JSON_VALUE(@JSON, '$.mparent.oDates[1].actual') AS actual  
    

    200440-image.png

    Best regards,
    Bert Zhou


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. arkiboys 9,686 Reputation points
    2022-05-10T11:44:11.627+00:00

    thank you all