Reading JSON array as one of the SQL table column

user_2627292 0
Viewed 9811

I am trying to read a json array into a table, one of the nodes (Languages) in the array is an array in itself, and I am getting null for this particular column (Languages).

Below is the sample json:

DECLARE @json NVARCHAR(MAX) = '[
  {
     "Id":1,
     "Name":"Test1",
     "Languages":["L1", "L2"]
  },
  {
     "Id":2,
     "Name":"Test2",
     "Languages":["L3", "L4"]
  },
  {
     "Id":3,
     "Name":"Test2",
     "Languages":["L5", "L6"]
  }]'

Below is the query I am using:

SELECT Id
     , Name 
     , Languages
  FROM OPENJSON(@json)
  WITH (Id INT '$.Id'
     , Name VARCHAR(20) '$.Name'
     , Languages VARCHAR(200) '$.Languages')

Below is the current result:

enter image description here

However I need the result as below

enter image description here

What am I doing wrong? Please help.

tsql sql-server arrays sql json
2 Answers

You can use NVARCHAR(max) as json for Language item inside WITH clause.

From Microsoft docs (all the details can be found here):

If you don't specify AS JSON for a column, the function returns a scalar value (for example, int, string, true, false) from the specified JSON property on the specified path. If the path represents an object or an array, and the property can't be found at the specified path, the function returns null in lax mode or returns an error in strict mode. This behavior is similar to the behavior of the JSON_VALUE function.

So your query should look like this:

SELECT Id
     , Name 
     , Languages
  FROM OPENJSON(@json)
  WITH (Id INT '$.Id'
     , Name VARCHAR(20) '$.Name'
     , Languages NVARCHAR(max) as json)

Results:

enter image description here

user_9097420

I hope maybe this query will be help you. Result is little bit different from you want.

DECLARE @json NVARCHAR(MAX) = '{"UserLang":[
  {
     "Id":1,
     "Name":"Test1",
     "Languages":["L1", "L2"]
  },
  {
     "Id":2,
     "Name":"Test2",
     "Languages":["L3", "L4"]
  },
  {
     "Id":3,
     "Name":"Test2",
     "Languages":["L5", "L6"]
  }]}'

SELECT 
JSON_VALUE(d.value,'$.Id') AS Id,
JSON_VALUE(d.value,'$.Name') AS Languages,
l.value AS Name
  FROM OPENJSON(@json,'$.UserLang') AS d CROSS APPLY OPENJSON (d.value,'$.Languages') AS l
user_103739900
Related