2.4 Query temporal tables, XML and JSON

Temporal tables

What is?

Temporal tables is a new resource of MSSQL 2016, we can store changes of data about a table, like auditing data. Every change in the data, are storaged into a historical table. Is very usefull to see in a specific range of data, the changes in the data.

Do it!

Well, the Temporal Table have some prerequisites. The table that you wanna trasform into a temporal table should have two columns datetime2, one that storage the start time and another to storage the end time. Some clauses should be included to create this columns, lets see the commands to create a temporal table:

CREATE TABLE dbo.Person (
  person_id  int identity(1,1) primary key --//need a primary key
 ,first_name varchar(50)
 ,midle_name varchar(50)
 ,last_name  varchar(50)
 ,birthdate  date
 ,startdate  datetime2(3) GENERATED ALWAYS AS ROW START NOT NULL
 ,enddate    datetime2(3) GENERATED ALWAYS AS ROW END NOT NULL
 ,PERIOD FOR SYSTEM_TIME (startdate, enddate)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Person_History)) --//SQL Server generate automatically a history table for us

Note the clauses GENERATED ALWAYS AS ROW [START | END], PERIOD FOR SYSTEM_TIME and SYSTEM_VERSIONING = ON, with this commands we can create a temporal table, with the fist command we defined the columns that control the start and end date for a row, with the PERIOD FOR SYSTEM TIME informs the the start and end columns and turn on the SYSTEM_VERSIONING, if you don't inform a table the SQL Server create a table for you. Otherwise we can transform a normal table to a temporal table too, let's code:

CREATE TABLE dbo.Car(
  car_id   integer identity(1,1) primary key
 ,model    varchar(100)
 ,car_year smallint
);

INSERT INTO dbo.Car(model, car_year)
VALUES('Test Model', 2005);

--//if your table have any rows you need a default constraint

ALTER TABLE dbo.Car
ADD startdate datetime2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT DF_Car_startdate DEFAULT ('19000101 00:00:00.000')
   ,enddate  datetime2(3) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT DF_Car_enddate DEFAULT ('99991231 23:59:59.999')
   ,PERIOD FOR SYSTEM_TIME(startdate, enddate);

ALTER TABLE dbo.Car
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Car_History));

Observe the HIDDEN clause, when we query this table with SELECT * FROM dbo.Car the columns startdate and enddate will not be displayed.

If you need drop a temporal table, first turn off the SYSTEM_VERSIONING, doing this the history table turn to a normal table, and you need to delete that table too:

ALTER TABLE dbo.Person
SET (SYSTEM_VERSIONING = OFF);

DROP TABLE dbo.Person;
DROP TABLE dbo.Person_History;

Whell, created the table, let's modify some data, first populate the person table:

INSERT INTO dbo.Person (first_name, midle_name, last_name, birthdate)
VALUES('Adrian', 'Hideki', 'San', '19981231');

INSERT INTO dbo.Person (first_name, midle_name, last_name, birthdate)
VALUES('Maria', 'San', 'Desu', '20000126');

INSERT INTO dbo.Person (first_name, midle_name, last_name, birthdate)
VALUES('Ronald', 'Fenomeno', 'IX', '19781025');

Now, look at the data into Person table:

person_id   first_name   midle_name   last_name  birthdate  startdate                   enddate
----------- ------------ ------------ ---------- ---------- --------------------------- ---------------------------
1           Adrian       Hideki       San        1998-12-31 2018-11-26 00:31:47.440     9999-12-31 23:59:59.999
2           Maria        San          Desu       2000-01-26 2018-11-26 00:32:19.103     9999-12-31 23:59:59.999
3           Ronald       Fenomeno     IX         1978-10-25 2018-11-26 00:32:55.487     9999-12-31 23:59:59.999

When we insert data into a temporal table, the history table don't add the inserted data, only deleted and updated rows are storaged into the history table, a interesting thing is the startdate, this field show when the record was inserted. Let's try modify some data and look at Person and Person_History table:

UPDATE dbo.Person
SET midle_name = 'Bideki'
WHERE person_id = 1

SELECT *
FROM dbo.Person

This results:

Person_id   first_name   midle_name  last_name  birthdate  startdate                   enddate
----------- ------------ ----------- ---------- ---------- --------------------------- ---------------------------
1           Adrian       Bideki      San        1998-12-31 2018-11-27 00:22:43.911     9999-12-31 23:59:59.999
2           Maria        San         Desu       2000-01-26 2018-11-26 00:32:19.103     9999-12-31 23:59:59.999
3           Ronald       Fenomeno    IX         1978-10-25 2018-11-26 00:32:55.487     9999-12-31 23:59:59.999

The start date has change, let's see the history table:

SELECT *
FROM dbo.Person_History;

person_id   first_name  midle_name  last_name   birthdate  startdate                   enddate
----------- ----------- ----------- ----------- ---------- --------------------------- ---------------------------
1           Adrian      Hideki      San         1998-12-31 2018-11-26 00:31:47.440     2018-11-27 00:22:43.911

Observe the startdate and enddate columns, the value represents of this columns representes the time when they are modified, the stardate presents the value of the last update, the enddate represents the actual update. Let's modify and delete some data:

UPDATE dbo.Person
SET midle_name = 'Hideki'
WHERE person_id = 1

DELETE FROM dbo.Person
WHERE person_id > 1

Look the Person and Person_History tables:

SELECT * FROM dbo.Person

SELECT * FROM dbo.Person_History

person_id   first_name  midle_name  last_name  birthdate  startdate                enddate
----------- ----------- ----------- ---------- ---------- ------------------------ ------------------------
1           Adrian      Hideki      San        1998-12-31 2018-11-27 00:57:16.226  9999-12-31 23:59:59.999

person_id   first_name  midle_name  last_name  birthdate  startdate                enddate
----------- ----------- ----------- ---------- ---------- ------------------------ ------------------------
1           Adrian      Hideki      San        1998-12-31 2018-11-26 00:31:47.440  2018-11-27 00:22:43.911
1           Adrian      Bideki      San        1998-12-31 2018-11-27 00:22:43.911  2018-11-27 00:57:16.226
2           Maria       San         Desu       2000-01-26 2018-11-26 00:32:19.103  2018-11-27 00:57:17.287
3           Ronald      Fenomeno    IX         1978-10-25 2018-11-26 00:32:55.487  2018-11-27 00:57:17.287

The deleted\updated records are storage into Person_History. We can note, when the data are modified the data are logged into history table. The columns startdate, enddate helps to know when the data are modified at a specif time. The enddate, represents when the transaction are commited, if we open a transaction and touch two records into a same transaction the enddate column should be storaged with the same date and time, but first let's recovery our deleted data:

SET IDENTITY_INSERT dbo.Person ON;

INSERT INTO dbo.Person(
  person_id
 ,first_name
 ,midle_name
 ,last_name
 ,birthdate)
SELECT person_id
      ,first_name
      ,midle_name
      ,last_name
      ,birthdate
FROM dbo.Person_History
WHERE person_id > 1;

SET IDENTITY_INSERT dbo.Person OFF;

BEGIN TRAN;

UPDATE dbo.Person
  SET midle_name = 'Xideki'
WHERE person_id = 1;

UPDATE dbo.Person
  SET last_name = 'Chan'
WHERE person_id = 2;

COMMIT;

Look at Person and Person_History tables:

SELECT * FROM dbo.Person WHERE person_id < 3;
SELECT * FROM dbo.Person_History WHERE person_id < 3 AND enddate >= '2018-11-27 01:09:56.417';

person_id   first_name   midle_name  last_name  birthdate  startdate                enddate
----------- ------------ ----------- ---------- ---------- ------------------------ ------------------------
1           Adrian       Xideki      San        1998-12-31 2018-11-27 01:09:56.417  9999-12-31 23:59:59.999
2           Maria        San         Chan       2000-01-26 2018-11-27 01:09:56.417  9999-12-31 23:59:59.999

person_id   first_name  midle_name  last_name  birthdate  startdate                enddate
----------- ----------- ----------- ---------- ---------- ------------------------ ------------------------
1           Adrian      Hideki      San        1998-12-31 2018-11-27 00:57:16.226  2018-11-27 01:09:56.417
2           Maria       San         Desu       2000-01-26 2018-11-27 01:08:13.136  2018-11-27 01:09:56.417

The startdate in the Person table show the same value for the two records, and the column enddate of Person_History table too.

Especif temporal table commands

The clause to query specific records from history table is FOR SYSTEM_TIME, you can issue a @date and rows that @date >= startdate AND @date < enddate will be returned. Let's see on pratice:

SELECT *
FROM dbo.Person --//query the original table with SYSTEM_TIME for retrieve data of history table
FOR SYSTEM_TIME AS OF '20181127 00:00:00.000'

SELECT *
FROM dbo.Person_History
WHERE '20181127 00:00:00.000' >= startdate AND '20181127 00:00:00.000' < enddate

person_id   first_name  midle_name  last_name  birthdate  startdate                   enddate
----------- ----------- ----------- ---------- ---------- --------------------------- ------------------------
1           Adrian      Hideki      San        1998-12-31 2018-11-26 00:31:47.440     2018-11-27 00:22:43.911
2           Maria       San         Desu       2000-01-26 2018-11-26 00:32:19.103     2018-11-27 00:57:17.287
3           Ronald      Fenomeno    IX         1978-10-25 2018-11-26 00:32:55.487     2018-11-27 00:57:17.287

person_id   first_name  midle_name  last_name  birthdate  startdate                   enddate
----------- ----------- ----------- ---------- ---------- --------------------------- ------------------------
1           Adrian      Hideki      San        1998-12-31 2018-11-26 00:31:47.440     2018-11-27 00:22:43.911
2           Maria       San         Desu       2000-01-26 2018-11-26 00:32:19.103     2018-11-27 00:57:17.287
3           Ronald      Fenomeno    IX         1978-10-25 2018-11-26 00:32:55.487     2018-11-27 00:57:17.287

The two commands returns the same result. Is possible use the clauses FROM and BETWEEN with SYSTEM_TIME, let's query:

SELECT *
FROM dbo.Person
FOR SYSTEM_TIME FROM '20181127 00:32:19.103' TO '20181127 00:57:17.287'

SELECT *
FROM dbo.Person_History
WHERE startdate BETWEEN '20181127 00:32:19.103' AND '20181127 00:57:17.287'
   OR enddate   BETWEEN '20181127 00:32:19.103' AND '20181127 00:57:17.287'

This results:

person_id   first_name  midle_name  last_name  birthdate  startdate                enddate
----------- ----------- ----------- ---------- ---------- ------------------------ ------------------------
1           Adrian      Bideki      San        1998-12-31 2018-11-27 00:22:43.911  2018-11-27 00:57:16.226
2           Maria       San         Desu       2000-01-26 2018-11-26 00:32:19.103  2018-11-27 00:57:17.287
3           Ronald      Fenomeno    IX         1978-10-25 2018-11-26 00:32:55.487  2018-11-27 00:57:17.287
1           Adrian      Hideki      San        1998-12-31 2018-11-27 00:57:16.226  2018-11-27 01:09:56.417

person_id   first_name  midle_name  last_name  birthdate  startdate                enddate
----------- ----------- ----------- ---------- ---------- ------------------------ ------------------------
1           Adrian      Bideki      San        1998-12-31 2018-11-27 00:22:43.911  2018-11-27 00:57:16.226
2           Maria       San         Desu       2000-01-26 2018-11-26 00:32:19.103  2018-11-27 00:57:17.287
3           Ronald      Fenomeno    IX         1978-10-25 2018-11-26 00:32:55.487  2018-11-27 00:57:17.287
1           Adrian      Hideki      San        1998-12-31 2018-11-27 00:57:16.226  2018-11-27 01:09:56.417

Using BETWEEN clause:

SELECT *
FROM dbo.Person
FOR SYSTEM_TIME BETWEEN '20181127 00:32:19.103' AND '20181127 00:57:17.287'

--//equivalent command
SELECT *
FROM dbo.Person_History
WHERE startdate <= '20181127 00:57:17.287'
  AND enddate > '20181127 00:32:19.103'

Results:

person_id   first_name  midle_name  last_name  birthdate  startdate                 enddate
----------- ----------- ----------- ---------- ---------- ------------------------- ---------------------------
1           Adrian      Bideki      San        1998-12-31 2018-11-27 00:22:43.911   2018-11-27 00:57:16.226
2           Maria       San         Desu       2000-01-26 2018-11-26 00:32:19.103   2018-11-27 00:57:17.287
3           Ronald      Fenomeno    IX         1978-10-25 2018-11-26 00:32:55.487   2018-11-27 00:57:17.287
1           Adrian      Hideki      San        1998-12-31 2018-11-27 00:57:16.226   2018-11-27 01:09:56.417

person_id   first_name  midle_name  last_name  birthdate  startdate                 enddate
----------- ----------- ----------- ---------- ---------- ------------------------- ---------------------------
1           Adrian      Bideki      San        1998-12-31 2018-11-27 00:22:43.911   2018-11-27 00:57:16.226
2           Maria       San         Desu       2000-01-26 2018-11-26 00:32:19.103   2018-11-27 00:57:17.287
3           Ronald      Fenomeno    IX         1978-10-25 2018-11-26 00:32:55.487   2018-11-27 00:57:17.287
1           Adrian      Hideki      San        1998-12-31 2018-11-27 00:57:16.226   2018-11-27 01:09:56.417

The CONTAIED IN clause get a limit of data about the history table:

DECLARE @start datetime2 = '20181127 00:32:19.103'
       ,@end   datetime2 = '20181127 01:57:17.287'

--//Note: The datetime type rouds the ms to 000, 003, 007. Ex 104 ms = 103 ms; 105ms = 107ms;

SELECT *
FROM dbo.Person
FOR SYSTEM_TIME CONTAINED IN (@start,@end);

SELECT *
FROM dbo.Person_History
WHERE startdate >= @start
  AND enddate <= @end;

person_id   first_name  midle_name  last_name  birthdate  startdate                enddate
----------- ----------- ----------- ---------- ---------- ------------------------ ------------------------
1           Adrian      Hideki      San        1998-12-31 2018-11-27 00:57:16.226  2018-11-27 01:09:56.417
2           Maria       San         Desu       2000-01-26 2018-11-27 01:08:13.136  2018-11-27 01:09:56.417

person_id   first_name  midle_name  last_name  birthdate  startdate                enddate
----------- ----------- ----------- ---------- ---------- ------------------------ ------------------------
1           Adrian      Hideki      San        1998-12-31 2018-11-27 00:57:16.226  2018-11-27 01:09:56.417
2           Maria       San         Desu       2000-01-26 2018-11-27 01:08:13.136  2018-11-27 01:09:56.417

The last specific command is the ALL, returns all records from the temporal and history table:

SELECT *
FROM dbo.Person
FOR SYSTEM_TIME ALL

SELECT *
FROM dbo.Person
UNION ALL
SELECT *
FROM dbo.Person_History

person_id   first_name  midle_name  last_name  birthdate  startdate                   enddate
----------- ----------- ----------- ---------- ---------- --------------------------- ---------------------------
1           Adrian      Xideki      San        1998-12-31 2018-11-27 01:09:56.417     9999-12-31 23:59:59.999
2           Maria       San         Chan       2000-01-26 2018-11-27 01:09:56.417     9999-12-31 23:59:59.999
3           Ronald      Fenomeno    IX         1978-10-25 2018-11-27 01:08:13.136     9999-12-31 23:59:59.999
1           Adrian      Hideki      San        1998-12-31 2018-11-26 00:31:47.440     2018-11-27 00:22:43.911
1           Adrian      Bideki      San        1998-12-31 2018-11-27 00:22:43.911     2018-11-27 00:57:16.226
2           Maria       San         Desu       2000-01-26 2018-11-26 00:32:19.103     2018-11-27 00:57:17.287
3           Ronald      Fenomeno    IX         1978-10-25 2018-11-26 00:32:55.487     2018-11-27 00:57:17.287
1           Adrian      Hideki      San        1998-12-31 2018-11-27 00:57:16.226     2018-11-27 01:09:56.417
2           Maria       San         Desu       2000-01-26 2018-11-27 01:08:13.136     2018-11-27 01:09:56.417

(9 rows affected)

person_id   first_name  midle_name  last_name  birthdate  startdate                   enddate
----------- ----------- ----------- ---------- ---------- --------------------------- ---------------------------
1           Adrian      Xideki      San        1998-12-31 2018-11-27 01:09:56.417     9999-12-31 23:59:59.999
2           Maria       San         Chan       2000-01-26 2018-11-27 01:09:56.417     9999-12-31 23:59:59.999
3           Ronald      Fenomeno    IX         1978-10-25 2018-11-27 01:08:13.136     9999-12-31 23:59:59.999
1           Adrian      Hideki      San        1998-12-31 2018-11-26 00:31:47.440     2018-11-27 00:22:43.911
1           Adrian      Bideki      San        1998-12-31 2018-11-27 00:22:43.911     2018-11-27 00:57:16.226
2           Maria       San         Desu       2000-01-26 2018-11-26 00:32:19.103     2018-11-27 00:57:17.287
3           Ronald      Fenomeno    IX         1978-10-25 2018-11-26 00:32:55.487     2018-11-27 00:57:17.287
1           Adrian      Hideki      San        1998-12-31 2018-11-27 00:57:16.226     2018-11-27 01:09:56.417
2           Maria       San         Desu       2000-01-26 2018-11-27 01:08:13.136     2018-11-27 01:09:56.417

(9 rows affected)

Query JSON

In the SQL Server 2016 whe can manage JSON like XML, isn't xquery but have specific commands, let's try generate some JSON:

SELECT *
FROM dbo.Person
FOR JSON AUTO;

With the JSON AUTO we generate this formated JSON:

[{
 "person_id":1,
 "first_name":"Adrian",
 "midle_name":"Xideki",
 "last_name":"San",
 "birthdate":"1998-12-31",
 "startdate":"2018-11-27T01:09:56.417",
 "enddate":"9999-12-31T23:59:59.999"
},{
 "person_id":2,
 "first_name":"Maria",
 "midle_name":"San",
 "last_name":"Chan",
 "birthdate":"2000-01-26",
 "startdate":"2018-11-27T01:09:56.417",
 "enddate":"9999-12-31T23:59:59.999"
},{
 "person_id":3,
 "first_name":"Ronald",
 "midle_name":"Fenomeno",
 "last_name":"IX",
 "birthdate":"1978-10-25",
 "startdate":"2018-11-27T01:08:13.136",
 "enddate":"9999-12-31T23:59:59.999"
}]

It's possible generate formated json's, like add a path to a JSON query:

DECLARE @j nvarchar(max);

SET @j = (SELECT *
          FROM dbo.Person
          FOR JSON PATH, ROOT('Person'));

SELECT @j

This command generates the following json:

{"Person":[
  {
   "person_id":1,
   "first_name":"Adrian",
   "midle_name":"Xideki",
   "last_name":"San",
   "birthdate":"1998-12-31",
   "startdate":"2018-11-27T01:09:56.417",
   "enddate":"9999-12-31T23:59:59.999"
  },{
   "person_id":2,
   "first_name":"Maria",
   "midle_name":"San",
   "last_name":"Chan",
   "birthdate":"2000-01-26",
   "startdate":"2018-11-27T01:09:56.417",
   "enddate":"9999-12-31T23:59:59.999"
  },{
   "person_id":3,
   "first_name":"Ronald",
   "midle_name":"Fenomeno",
   "last_name":"IX",
   "birthdate":"1978-10-25",
   "startdate":"2018-11-27T01:08:13.136",
   "enddate":"9999-12-31T23:59:59.999"
  }]
}

Observes the Person path, that whe specified into ROOT clause. Let's build a specific JSON structure:

SELECT [person.id] = person_id
      ,[person.full_name] = first_name + ' ' + midle_name + ' ' + last_name
      ,[person.age] = DATEDIFF(YEAR, birthdate, GETDATE())
      ,[person.period.start] = startdate
      ,[person.period.end] = enddate
FROM dbo.Person
FOR JSON PATH, ROOT('Persons')

Generating this JSON:

{
 "Persons":[
    {
       "person":{
          "id":1,
          "full_name":"Adrian Xideki San",
          "age":20,
          "period":{
             "start":"2018-11-27T01:09:56.417",
             "end":"9999-12-31T23:59:59.999"
          }
       }
    },
    {
       "person":{
          "id":2,
          "full_name":"Maria San Chan",
          "age":18,
          "period":{
             "start":"2018-11-27T01:09:56.417",
             "end":"9999-12-31T23:59:59.999"
          }
       }
    },
    {
       "person":{
          "id":3,
          "full_name":"Ronald Fenomeno IX",
          "age":40,
          "period":{
             "start":"2018-11-27T01:08:13.136",
             "end":"9999-12-31T23:59:59.999"
          }
       }
    }
 ]
}

Whe defiened the JSON format into alias column, each dot represents a atribute, look at period properties, they have the star and end informations, whe can generate this information into another {}:

SELECT [person.id] = person_id
      ,[person.full_name] = first_name + ' ' + midle_name + ' ' + last_name
      ,[person.age] = DATEDIFF(YEAR, birthdate, GETDATE())
      ,[period.start] = startdate
      ,[period.end] = enddate
FROM dbo.Person
FOR JSON PATH, ROOT('Persons')

{
 "Persons":[
    {
       "person":{
          "id":1,
          "full_name":"Adrian Xideki San",
          "age":20
       },
       "period":{
          "start":"2018-11-27T01:09:56.417",
          "end":"9999-12-31T23:59:59.999"
       }
    },
    {
       "person":{
          "id":2,
          "full_name":"Maria San Chan",
          "age":18
       },
       "period":{
          "start":"2018-11-27T01:09:56.417",
          "end":"9999-12-31T23:59:59.999"
       }
    },
    {
       "person":{
          "id":3,
          "full_name":"Ronald Fenomeno IX",
          "age":40
       },
       "period":{
          "start":"2018-11-27T01:08:13.136",
          "end":"9999-12-31T23:59:59.999"
       }
    }
 ]
}

Now the period propertie aren't into person propertie. Each row are one whole, delimited by {} and the resultset are delimited by a []. With this json we can query a json to work like a normal result set. A interesting thing about work with json into sql server is there isn't a specific type like xml, whe need store the json into a nvarchar field. With the OPEN_JSON clause whe can transform a JSON doc to a table:

DECLARE @json NVARCHAR(4000) = N'{  
   "StringValue":"John",  
   "IntValue":45,  
   "TrueValue":true,  
   "FalseValue":false,  
   "NullValue":null,  
   "ArrayValue":["a","r","r","a","y"],  
   "ObjectValue":{"obj":"ect"}  
}'

SELECT *
FROM OPENJSON(@json)

key             value                    type
--------------- ------------------------ ----
StringValue     John                     1
IntValue        45                       2
TrueValue       true                     3
FalseValue      false                    3
NullValue       NULL                     0
ArrayValue      ["a","r","r","a","y"]    4
ObjectValue     {"obj":"ect"}            5

This function returns three columns, the key, value and type, the type is the most curiosus value, in the SQL Server the JSON only can be returned into 6 distincts types:

Type value	JSON data type
0	null
1	string
2	int
3	true/false
4	array
5	object

Look to the type 4 and 5, a array/object value, we can query directly using OPEN_JSON and path expressions:

DECLARE @json NVARCHAR(4000) = N'{  
   "StringValue":"John",  
   "IntValue":45,  
   "TrueValue":true,  
   "FalseValue":false,  
   "NullValue":null,  
   "ArrayValue":["a","r","r","a","y"],  
   "ObjectValue":{"obj":"ect"}  
   }' 
       ,@json2 NVARCHAR(4000) = N'{  
   "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
   }';
  
SELECT *
FROM OPENJSON(@json, '$."ArrayValue"')

SELECT *
FROM OPENJSON(@json, '$."ObjectValue"')

SELECT [key], value
FROM OPENJSON(@json2,'$.path.to."sub-object"')

key                 value    type
------------------- -------- ----
0                   a        1
1                   r        1
2                   r        1
3                   a        1
4                   y        1

(5 rows affected)

key                 value    type
------------------- -------- ----
obj                 ect      1

(1 row affected)

key                 value
------------------- --------
0                   en-GB
1                   en-UK
2                   de-AT
3                   es-AR
4                   sr-Cyrl

(5 rows affected)

It's possible define the types and column name for the fields that will be returned from a JSON file, using the WITH clause, let's see:

DECLARE @json NVARCHAR(4000) = N'{  
   "StringValue":"John",  
   "IntValue":45,  
   "TrueValue":true,  
   "FalseValue":false,  
   "NullValue":null,  
   "ArrayValue":["a","r","r","a","y"],  
   "ObjectValue":{"obj":"ect"}  
   }';
  
SELECT *
FROM OPENJSON(@json)
WITH (
--//column  | type        | json root      | JSON formated? |
 StringValue varchar(100)  '$.StringValue',
 IntValue    int           '$.IntValue',
 TrueValue   bit           '$.TrueValue',
 FalseValue  bit           '$.FalseValue',
 ArrayValue  nvarchar(MAX) '$.ArrayValue'     AS JSON,
 ObjectValue nvarchar(MAX) '$.ObjectValue'    AS JSON
)

StringValue   IntValue    TrueValue FalseValue ArrayValue             ObjectValue
------------- ----------- --------- ---------- ---------------------- -------------
John          45          1         0          ["a","r","r","a","y"]  {"obj":"ect"}

(1 row affected)

Note the clause AS JSON, when specified the piece declared will be returned JSON formated. The functions JSON_VALUE and JSON_QUERY returns data from JSON text, the JSON_VALUE is to return scalar values (like integer, string, date), and JSON_QUERY is used into ArrayValue and Object values about JSON properties:

DECLARE @json nvarchar(MAX) = '{
   "Persons":[
      {
         "person":{
            "id":1,
            "full_name":"Adrian Xideki San",
            "age":20,
            "period":{
               "start":"2018-11-27T01:09:56.417",
               "end":"9999-12-31T23:59:59.999"
            }
         }
      },
      {
         "person":{
            "id":2,
            "full_name":"Maria San Chan",
            "age":18,
            "period":{
               "start":"2018-11-27T01:09:56.417",
               "end":"9999-12-31T23:59:59.999"
            }
         }
      },
      {
         "person":{
            "id":3,
            "full_name":"Ronald Fenomeno IX",
            "age":40,
            "period":{
               "start":"2018-11-27T01:08:13.136",
               "end":"9999-12-31T23:59:59.999"
            }
         }
      }
   ]
  }';

SELECT JSON_VALUE(tb.value, '$.person.age') AS Age --returns the person age
      ,JSON_QUERY(tb.value, '$.person.period') AS periodObject --returns the period object
      ,JSON_VALUE(tb.value, '$.person.period.start') AS periodStartDate --returns the startdate
      ,JSON_VALUE(JSON_QUERY(tb.value, '$.person'), '$.full_name') AS fullName --//combine the JSON_QUERY and JSON_VALUE
FROM OPENJSON(@json, '$.Persons') AS tb;

Returns:

Age	Period Object	Period Start Date	Full name
20	{ "start":"2018-11-27T01:09:56.417", "end":"9999-12-31T23:59:59.999"}	2018-11-27T01:09:56.417	Adrian Xideki San
18	{ "start":"2018-11-27T01:09:56.417", "end":"9999-12-31T23:59:59.999"}	2018-11-27T01:09:56.417	Maria San Chan
40	{ "start":"2018-11-27T01:08:13.136", "end":"9999-12-31T23:59:59.999"}	2018-11-27T01:08:13.136	Ronald Fenomeno IX

If you try to query a object or array propertie with JSON_VALUE the function will return NULL, the same thing that you try to query a scalar value with JSON_QUERY.

DECLARE @json nvarchar(MAX) = 
'{
   "Persons":[
      {
         "person":{
            "id":1,
            "full_name":"Adrian Xideki San",
            "age":20,
            "period":{
               "start":"2018-11-27T01:09:56.417",
               "end":"9999-12-31T23:59:59.999"
            }
         }
      }
   ]
  }';

SELECT JSON_QUERY(tb.value, '$.person.age') AS Age --using the JSON_QUERY to a scalar value
      ,JSON_VALUE(tb.value, '$.person.period') AS periodObject --using the JSON_VALUE to a object value
      --//correct use
      ,JSON_VALUE(tb.value, '$.person.age') AS Age --using the JSON_QUERY to a scalar value
      ,JSON_QUERY(tb.value, '$.person.period') AS periodObject --using the JSON_VALUE to a object value
FROM OPENJSON(@json, '$.Persons') AS tb;

Age	Period Object	Age	Period Object
NULL	NULL	20	{ "start":"2018-11-27T01:09:56.417", "end":"9999-12-31T23:59:59.999" }

References

Temporal tables - Microsoft Documentation Open JSON

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

2.4.Query_temporal_and_non-relational_data.md

2.4.Query_temporal_and_non-relational_data.md

2.4 Query temporal tables, XML and JSON

Temporal tables

What is?

Do it!

Especif temporal table commands

Query JSON

References

Files

2.4.Query_temporal_and_non-relational_data.md

Latest commit

History

2.4.Query_temporal_and_non-relational_data.md

File metadata and controls

2.4 Query temporal tables, XML and JSON

Temporal tables

What is?

Do it!

Especif temporal table commands

Query JSON

References