SQL query to JSON data

Question

We've JSON entry in the MS SQL database. I would like to export JSON entry in the "Data" column which match with the list of EMPNO. Could someone please help?

ColumnName: Data

Data inside the column:

output:{
   "Request":{
      "Person":{
         "DisplayName":"Test User",
         "EMPNO":"000001",
         "Entity":"01",
         "Country":"AA"
      },
      "DomainOverride":null,
      "ReasonForGen":"Only",
      "Email":"[email protected]",
      "CurrentSIP":"[email protected]"
   },
   "EmailAddress":"123@testcom",
   "SIPAddress":"[email protected]",
   "Status":"NoChange"
}

Query in layman language:

select DisplayName,EMPNO,Entity,Country,DomainOverride,ReasonForGen,Email
from Table1
where data.output.Request.EMPNO in ([EMPNO list])

nunoq · Accepted Answer · 2019-12-20 11:53:33Z

1

You can use JSON_VALUE. Something like this:

select JSON_VALUE(data,'$.Output.Request.Person.DisplayName'), ...
from Table1
where JSON_VALUE(data,'$.Output.Request.Person.EMPNO') in ([EMPNO list])

edited Dec 20, 2019 at 11:53

answered Dec 20, 2019 at 11:15

nunoq

5502 silver badges7 bronze badges

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

Comments

Zhorov · Accepted Answer · 2020-03-20 08:17:09Z

You may try to use OPENJSON() to parse the JSON text and get objects and values from the JSON input as a table. You need to use OPENJSON() with explicit schema in the WITH clause to define the columns:

Table:

CREATE TABLE Data (
   JsonData nvarchar(max)
)
INSERT INTO Data 
   (JsonData)
VALUES
   (N'{
   "output":{
      "Request":{
         "Person":{
            "DisplayName":"Test User",
            "EMPNO":"000001",
            "Entity":"01",
            "Country":"AA"
         },
         "DomainOverride":null,
         "ReasonForGen":"Only",
         "Email":"[email protected]",
         "CurrentSIP":"[email protected]"
      },
      "EmailAddress":"123@testcom",
      "SIPAddress":"[email protected]",
      "Status":"NoChange"
   }
}')

Statement:

SELECT
   j.DisplayName,
   j.EMPNO,
   j.Entity,
   j.Country,
   j.DomainOverride,
   j.ReasonForGen,
   j.Email
FROM Data d
CROSS APPLY OPENJSON(d.JsonData) WITH (
   EMPNO nvarchar(10) '$.output.Request.Person.EMPNO',
   DisplayName nvarchar(50) '$.output.Request.Person.DisplayName',
   EMPNO nvarchar(50) '$.output.Request.Person.EMPNO',
   Entity nvarchar(50) '$.output.Request.Person.Entity',
   Country nvarchar(50) '$.output.Request.Person.Country',
   DomainOverride nvarchar(50) '$.output.Request.DomainOverride',
   ReasonForGen nvarchar(50) '$.output.Request.ReasonForGen',
   Email nvarchar(50) '$.output.Request.Email'
) j
-- Use additional WHERE clause
--WHERE j.EMPNO IN ('00001', '000002')

Result:

DisplayName EMPNO   Entity  Country DomainOverride  ReasonForGen    Email
Test User   000001  01      AA                      Only            [email protected]

Collectives™ on Stack Overflow

SQL query to JSON data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related