Json Query

• vincenzo_capelli

  SSChasing Mays

  Points: 606

  More actions

  November 17, 2016 at 1:14 pm

  #337392

  Hi,

  I have a table with these columns:

  id,details

  the column details contains a JSON like this:

  [

  {"Name":"Test1","Templates":["UPDATE"],"Mail":"Test1@gmail.com"},

  {"Name":"Test2","Templates":["READ","DELETE"],"Mail":"Test2@gmail.com"}

  ]

  I would like to transform data inside this column into a tabular format.

  I'm completely new with Json query, I started with a query like this:

  SELECT Name,Templates,Mail

  FROM table f

  CROSS APPLY OPENJSON(f.detail)

  WITH (Name nvarchar(100) '$.Name',

  Templates nvarchar(max) '$.Templates',

  Mail nvarchar(100) '$.Mail'

  )

  but I obtained this:

  test1, null, test1@gmail.com

  test2, null, test2@gmail.com

  Can you help me to understand why with the query above I don't obtain:

  test1,"UPDATE",test1@gmail.com

  test2,"READ","DELETE",test2@gmail.com

  My final goal is to obtain a result like this:

  Name, Permission,mail

  so the output should be:

  test1,update,test1@gmail.com

  test2,read,test2@gmail.com

  test2,delete,test2@gmail.com

  Can you help me on this matter?

  Thanks

  Regards

• vincenzo_capelli

  SSChasing Mays

  Points: 606

  More actions

  November 18, 2016 at 3:13 am

  #1913358

  Solved!

  With this:

  SELECT Name,Templates,Mail

  FROM table f

  CROSS APPLY OPENJSON(f.detail)

  WITH (Name nvarchar(100) ,

  Templates nvarchar(max) AS JSON,

  Mail nvarchar(100)

  )

  I obtain this:

  test1,"UPDATE",test1@gmail.com

  test2,"READ","DELETE",test2@gmail.com

  with this:

  SELECT f.Name,secondlevel.value,f.Mail

  FROM table f

  CROSS APPLY OPENJSON(f.detail)

  WITH (Name nvarchar(100) ,

  Templates nvarchar(max) AS JSON,

  Mail nvarchar(100)

  ) as

  firstlevel

  CROSS APPLY OPENJSON(Templates) secondlevel

  I obtain this:

  test1,update,test1@gmail.com

  test2,read,test2@gmail.com

  test2,delete,test2@gmail.com

  Thanks

  Regards