Tutorial by Examples: on

All objects are instances of a class. However, that is not the whole truth. In Ruby, every object also has a somewhat hidden singleton class. This is what allows methods to be defined on individual objects. The singleton class sits between the object itself and its actual class, so all methods defi...

To detect multiple features at once, use the and operator. @supports (transform: translateZ(1px)) and (transform-style: preserve-3d) and (perspective: 1px) { /* Probably do some fancy 3d stuff here */ } There is also an or operator and a not operator: @supports (display: flex) or (display...

To better understand the semantics of conses and lists, a graphical representation of this kind of structures is often used. A cons cell is usually represented with two boxes in contact, that contain either two arrows that point to the car and cdr values, or directly the values. For instance, the re...

To consume a REST API with RestTemplate, create a Spring boot project with the Spring boot initialzr and make sure the Web dependency is added: <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dep...

JSON_VALUE function enables you to take a data from JSON text on the path specified as the second argument, and use this value in any part of the select query: select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type from Product where JSON_VALUE(Data, '$.Type') = 'part' ...

Once JSON values are extracted from JSON text, you can use them ina any part of the query. You can create some kind of report on JSON data with grouping aggregations, etc: select JSON_VALUE(Data, '$.Type') as type, AVG( cast(JSON_VALUE(Data, '$.ManufacturingCost') as float) ) as cost from...

If some JSON text might not be properly formatted, you can remove those entries from query using ISJSON function. select ProductID, Name, Color, Size, Price, JSON_VALUE(Data, '$.Type') as Type from Product where JSON_VALUE(Data, '$.Type') = 'part' and ISJSON(Data) > 0

JSON_MODIFY function can be used to update value on some path. You can use this function to modify original value of JSON cell in UPDATE statement: update Product set Data = JSON_MODIFY(Data, '$.Price', 24.99) where ProductID = 17; JSON_MODIFY function will update or create Price key (if it do...

JSON_MODIFY function can be used to append new value to some array inside JSON: update Product set Data = JSON_MODIFY(Data, 'append $.tags', "sales") where ProductID = 17; New value will be appended at the end of the array, or a new array with value ["sales"] will be create...

If you have a "child table" formatted as JSON collection and stored in-row as JSON column, you can unpack this collection, transform it to table and join it with parent row. Instead of the standard JOIN operator, you should use CROSS APPLY. In this example, product parts are formatted as ...

In this example, Tags array may contain various keywords like ["promo", "sales"], so we can open this array and filter values: select ProductID, Name, Color, Size, Price, Quantity from Product CROSS APPLY OPENJSON(Data, '$.Tags') where value = 'sales' OPENJSON will op...

JSON is textual format, so it is stored in standard NVARCHAR columns. NoSQL collection is equivalent to two column key value table: CREATE TABLE ProductCollection ( Id int identity primary key, Data nvarchar(max) ) Use nvarchar(max) as you are not sure what would be the size of your JSON ...

Since JSON is stored textual column, you might want to ensure that it is properly formatted. You can add CHECK constraint on JSON column that checks is text properly formatted JSON: CREATE TABLE ProductCollection ( Id int identity primary key, Data nvarchar(max) CONSTRAINT [Data shoul...

You can expose values from JSON column as computed columns: CREATE TABLE ProductCollection ( Id int identity primary key, Data nvarchar(max), Price AS JSON_VALUE(Data, '$.Price'), Color JSON_VALUE(Data, '$.Color') PERSISTED ) If you add PERSISTED computed column, value from JSON tex...

Queries that filter or sort data by some value in JSON column usually use full table scan. SELECT * FROM ProductCollection WHERE JSON_VALUE(Data, '$.Color') = 'Black' To optimize these kind of queries, you can add non-persisted computed column that exposes JSON expression used in filter or sort...

If you can use memory-optimized tables, you can store JSON as text: CREATE TABLE ProductCollection ( Id int identity primary key nonclustered, Data nvarchar(max) ) WITH (MEMORY_OPTIMIZED=ON) Advantages of JSON in in-memory: JSON data is always in memory so there is no disk access Ther...

OPENJSON function parse JSON text and returns all key:value pairs at the first level of JSON: declare @json NVARCHAR(4000) = N'{"Name":"Joe","age":27,"skills":["C#","SQL"]}'; SELECT * FROM OPENJSON(@json); keyvaluetypeNameJoe1age272ski...

OPENJSON function parses collection of JSON objects and returns values from JSON text as set of rows. declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00","Customer": "MSFT","Price":59.99,"Q...

OPENJSON function parses collection of JSON objects and returns values from JSON text as set of rows. If the values in input object are nested, additional mapping parameter can be specified in each column in WITH clause: declare @json nvarchar(4000) = N'[ {"data":{"num":&quot...

OPENJSON can extract fragments of JSON objects inside the JSON text. In the column definition that references JSON sub-object set the type nvarchar(max) and AS JSON option: declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00"...