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: allaboutmssql.com/2015/12/…

slon
– slon

2018-09-24 04:37:34 +00:00
Commented Sep 24, 2018 at 4:37 — slon
– slon, Commented Sep 24, 2018 at 4:37

Zaynul Abadin Tuhin · Accepted Answer · 2018-09-24 04:53:35Z

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, 2018 at 4:53

answered Sep 24, 2018 at 4:47

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

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

5 Comments

Zaynul Abadin Tuhin Over a year ago

@Md.AbdulAlim dbfiddle.uk/… could you please check this link

Md. Abdul Alim Over a year ago

I want get all data into GroupFields not only first value. How can?

Zaynul Abadin Tuhin Over a year ago

@Md.AbdulAlim dbfiddle.uk/… check the 2nd query in this link

Md. Abdul Alim Over a year ago

Thanks for your help. I got a solution by using your process

Md. Abdul Alim Over a year ago

Your answer is helpful but not complete. I voted your answer.

Md. Abdul Alim · Accepted Answer · 2018-09-24 05:32:45Z

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, 2018 at 5:32

answered Sep 24, 2018 at 5:24

Md. Abdul Alim

6991 gold badge6 silver badges20 bronze badges

Collectives™ on Stack Overflow

SQL Query into Json Data

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related