T-SQL read from JSON Array

Question

I have the following JSON

DECLARE @json NVARCHAR(max) = '
{
  "source": "one",
  "dataSetId":"a3d5-14fda14",
  "data": [
    {
      "FieldId": 10
    }
  ]
}
'

and need to parse the values to the table.

I have tried:

-- meta
SELECT *  
FROM OPENJSON(@json)  
  WITH ( source NVARCHAR(20)
        ,dataSetId NVARCHAR(50)
        ,FieldId INT '$.data.FieldId'
)

-- array
SELECT * 
FROM OPENJSON(@json, '$.data')
  WITH ( source NVARCHAR(20)
        ,dataSetId NVARCHAR(50)
        ,FieldId int '$.FieldId'
)

It works to get source and dataSetId columns or to get FieldId column, but I am still unable to merge both solution, to parse both non-array and array data.

The SELECT should return all the data in one query.

Yes, I did. You can see the queries in the question. There's no error. .. it works, but does not return all the data from JSON in one query. — DNac
– DNac, Commented Oct 5, 2017 at 8:53
Should you try with by defining a temp table and store your result into temp table i.e #myResultTable and pass it to OPENJSON — user4856296
– user4856296, Commented Oct 5, 2017 at 10:26

Ihtsham Minhas · Accepted Answer · 2017-10-05 12:38:28Z

1

There are many solutions for this question. one of them is....

DECLARE @json NVARCHAR(max) = '
 {
   "source": "one",
   "dataSetId":"a3d5-14fda14",
   "data": [
    {
      "FieldId": 10
    }
  ]
}'

SELECT  A.* , B.*  
FROM OPENJSON(@json) 
  WITH ( source NVARCHAR(20)
        ,dataSetId NVARCHAR(50)) A ,  
 OPENJSON(@json, '$.data') 
  WITH (  FieldId int '$.FieldId') B

edited Oct 5, 2017 at 12:38

answered Oct 5, 2017 at 12:28

Ihtsham Minhas

1,5141 gold badge20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL read from JSON Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related