Search code examples
sqlsql-server-2017json-query

SQL Query into Json Data

I have a table which have many column. One column has json value like

{
  "RequiredForCompany":1, 
  "RequiredOnScreen":0,
  "Editable":[],
  "Visible":[], 
  "Expression":{},
  "GroupFields":[142,156]
}

I want to query into this json value with selecting others column. My query like as 

SELECT 
    [Name],
    JSON_VALUE(FieldAttributes, '$.GroupFields') AS GroupFields
FROM 
    [std].[Field]

But it returns null for groupfields. Any way to query into this value?

Solution

  • Finally I got a solution by the help of @Zaynul Abadin Tuhin. My query is

    SELECT [Name], STUFF((SELECT ','+[Value] FROM 
  OPENJSON (FieldAttributes,'$.GroupFields') FOR XML PATH ('')),1,1,'') AS GroupFields
FROM [std].[Field]

    It's working fine.