MS SQL SERVER - Transact SQL and JSON fields

ms-sql-server-and-json-fields.md

Microsoft SQL Server and JSON fields

On SQL Server 2016 and Azure SQL database it's possible to treat JSON objects stored a strings inside a table. Cf. this: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-2017

Create a sample table

DROP TABLE IF EXISTS SeriesInfo;
GO
CREATE TABLE SeriesInfo (
    SeriesInfoId   BIGINT IDENTITY(1 , 1) ,
    GeneratedAt DATETIME2 NOT NULL,
    EntityCode  VARCHAR(50) NOT NULL, -- human_readable_id
    AlternativeId   INT NOT NULL,
    ShapeJson   NVARCHAR(MAX) NOT NULL,
    CONSTRAINT PK_SeriesInfo PRIMARY KEY CLUSTERED(SeriesInfoId), -- primary key
    CONSTRAINT UK_Alternative UNIQUE(EntityCode, AlternativeId),  -- alternative key
    CONSTRAINT C_ShapeIsJson CHECK(ISJSON(ShapeJson) > 0)         -- JSON validation
);
GO

Inject some normalised and some JSON data in the table

Imagine we want to store a time series as a list of key/value pairs representing half-hour intervals and a target value for that half-hour interval. So to represent a whole day as a time series we need to store a JSON array with 48 objects, each object representing the <X,Y> pairs of values to define the shape of the curve in the graph.

DECLARE @SeriesInfoShape NVARCHAR(MAX)
SET @SeriesInfoShape =
N'{
  "metric": "my_content_description",
  "shape": [
    {"halfhour_in_day_id": 1, "value":0.5},
    {"halfhour_in_day_id": 2, "value":0.5},
    {"halfhour_in_day_id": 3, "value":0.5},
    {"halfhour_in_day_id": 4, "value":0.5},
    {"halfhour_in_day_id": 5, "value":0.5},
    {"halfhour_in_day_id": 6, "value":0.5},
    {"halfhour_in_day_id": 7, "value":0.5},
    {"halfhour_in_day_id": 8, "value":0.5},
    {"halfhour_in_day_id": 9, "value":0.5},
    {"halfhour_in_day_id": 10, "value":0.5},
    {"halfhour_in_day_id": 11, "value":0.5},
    {"halfhour_in_day_id": 12, "value":0.5},
    {"halfhour_in_day_id": 13, "value":0.5},
    {"halfhour_in_day_id": 14, "value":0.5},
    {"halfhour_in_day_id": 15, "value":0.5},
    {"halfhour_in_day_id": 16, "value":0.5},
    {"halfhour_in_day_id": 17, "value":0.5},
    {"halfhour_in_day_id": 18, "value":0.5},
    {"halfhour_in_day_id": 19, "value":0.5},
    {"halfhour_in_day_id": 20, "value":0.5},
    {"halfhour_in_day_id": 21, "value":0.5},
    {"halfhour_in_day_id": 22, "value":0.5},
    {"halfhour_in_day_id": 23, "value":0.5},
    {"halfhour_in_day_id": 24, "value":0.5},
    {"halfhour_in_day_id": 25, "value":0.5},
    {"halfhour_in_day_id": 26, "value":0.5},
    {"halfhour_in_day_id": 27, "value":0.5},
    {"halfhour_in_day_id": 28, "value":0.5},
    {"halfhour_in_day_id": 29, "value":0.5},
    {"halfhour_in_day_id": 30, "value":0.5},
    {"halfhour_in_day_id": 31, "value":0.5},
    {"halfhour_in_day_id": 32, "value":0.5},
    {"halfhour_in_day_id": 33, "value":0.5},
    {"halfhour_in_day_id": 34, "value":0.5},
    {"halfhour_in_day_id": 35, "value":0.5},
    {"halfhour_in_day_id": 36, "value":0.5},
    {"halfhour_in_day_id": 37, "value":0.5},
    {"halfhour_in_day_id": 38, "value":0.5},
    {"halfhour_in_day_id": 39, "value":0.5},
    {"halfhour_in_day_id": 40, "value":0.5},
    {"halfhour_in_day_id": 41, "value":0.5},
    {"halfhour_in_day_id": 42, "value":0.5},
    {"halfhour_in_day_id": 43, "value":0.5},
    {"halfhour_in_day_id": 44, "value":0.5},
    {"halfhour_in_day_id": 45, "value":0.5},
    {"halfhour_in_day_id": 46, "value":0.5},
    {"halfhour_in_day_id": 47, "value":0.5},
    {"halfhour_in_day_id": 48, "value":0.5}
  ]
}'

INSERT INTO SeriesInfo (GeneratedAt, EntityCode, AlternativeId, ShapeJson)
VALUES (GETUTCDATE(), 'human_readable_id', 1, @SeriesInfoShape);

Query examples

SELECT * FROM SeriesInfo;

To extract from the JSON a SCALAR value for a field:

SELECT SeriesInfoId, JSON_VALUE(ShapeJson, '$.metric') AS Metric
FROM SeriesInfo;

To extract from the JSON a sub-structure as a JSON string (no scalar values involved here):

SELECT SeriesInfoId, JSON_QUERY(ShapeJson, '$.shape') AS Shape
FROM SeriesInfo;

To mime the de-normalization like when we JOIN multiple tables together:

SELECT SI.SeriesInfoId, JSON_VALUE(SI.ShapeJson, '$.metric') AS Metric, JS.*
FROM SeriesInfo AS SI
CROSS APPLY OPENJSON(SI.ShapeJson, '$.shape') WITH (
    HalfHourId INT '$.halfhour_in_day_id',
    MetricVal FLOAT '$.value'
) AS JS
WHERE SI.EntityCode = 'human_readable_id' AND JS.HalfHourId > 34 AND JS.HalfHourId <= 38;