SQL Query into Json Data

Question

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?

Here is good reference: http://www.allaboutmssql.com/2015/12/sql-server-json-examples-jsonquery.html — slon, Sep 24 '18 at 04:37

Zaynul Abadin Tuhin · Answer 1 · 2018-09-24T04:53:35.783

1

your GroupFields (array)is sequence values container that's why you have to select like below

DECLARE @jsonInfo1 NVARCHAR(MAX) =N'{
"RequiredForCompany":1,
"RequiredOnScreen":0,
"Editable":[], 
"Visible":[],
"Expression":{},
"GroupFields":[142,156]

}';


select JSON_VALUE(@jsonInfo1, '$.GroupFields[0]') as g;

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7eba8bf042a27253440ce4b41f440979

So in your case

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

edited Sep 24 '18 at 04:53

answered Sep 24 '18 at 04:47

Zaynul Abadin Tuhin

31,407
5
33
63

@Md.AbdulAlim https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7eba8bf042a27253440ce4b41f440979 could you please check this link – Zaynul Abadin Tuhin Sep 24 '18 at 04:57
I want get all data into GroupFields not only first value. How can? – Md. Abdul Alim Sep 24 '18 at 04:59
1

@Md.AbdulAlim https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=10d8365a85bc21fcbcef4c1068b104d8 check the 2nd query in this link – Zaynul Abadin Tuhin Sep 24 '18 at 05:09
Thanks for your help. I got a solution by using your process – Md. Abdul Alim Sep 24 '18 at 05:32
Your answer is helpful but not complete. I voted your answer. – Md. Abdul Alim Sep 24 '18 at 05:37

Md. Abdul Alim · Accepted Answer · 2018-09-24T05:32:45.843

0

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.

edited Sep 24 '18 at 05:32

answered Sep 24 '18 at 05:24

Md. Abdul Alim

707
1
6
19

SQL Query into Json Data

2 Answers2