Tutorial by Examples: r

Finding rows that contain value in the JSON array

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 stored as text column

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 ...

Ensure that JSON is properly formatted using ISJSON

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...

Expose values from JSON text as computed columns

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...

JSON stored in in-memory tables

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...

In/Out Parameters

PL/SQL uses IN, OUT, IN OUT keywords to define what can happen to a passed parameter. IN specifies that the parameter is read only and the value cannot be changed by the procedure. OUT specifies the parameter is write only and a procedure can assign a value to it, but not reference the value. IN ...

Get key:value pairs from JSON text

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...

Transform JSON array into set of rows

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...

Transform nested JSON fields into set of rows

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...

Extracting inner JSON sub-objects

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"...

Working with nested JSON sub-arrays

JSON may have complex structure with inner arrays. In this example, we have array of orders with nested sub array of OrderItems. declare @json nvarchar(4000) = N'[ {"Number":"SO43659","Date":"2011-05-31T00:00:00", "Items":[{"Price"...

RANK()

A RANK() Returns the rank of each row in the result set of partitioned column. Eg : Select Studentid,Name,Subject,Marks, RANK() over(partition by name order by Marks desc)Rank From Exam order by name,subject Studentid Name Subject Marks Rank 101 Ivan Maths ...

DENSE_RANK ()

Same as that of RANK(). It returns rank without any gaps: Select Studentid, Name,Subject,Marks, DENSE_RANK() over(partition by name order by Marks desc)Rank From Exam order by name Studentid Name Subject Marks Rank 101 Ivan Science 80 1 101 Ivan ...

Types and classifications of Trigger

In SQL Server, there are two categories of triggers: DDL Triggers and DML Triggers. DDL Triggers are fired in response to Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER and DROP. DML Triggers are fired ...

How to create a template

Setting up a template for one application Create a file named template.xhtml under the /WEB-INF folder, that way the template files will be accessible only for the framework. /WEB-INF/template.xhtml <!DOCTYPE html> <html lang="en" xmlns="http://www.w3.org/1999/xhtml&...

TRY PARSE

SQL Server 2012 It converts string data type to target data type(Date or Numeric). For example, source data is string type and we need to covert to date type. If conversion attempt fails it returns NULL value. Syntax: TRY_PARSE (string_value AS data_type [ USING culture ]) String_value – This is...

TRY CONVERT

SQL Server 2012 It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need date/integer format. Then this will help us to achieve the same. Syntax: TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] ) TRY_CON...

TRY CAST

SQL Server 2012 It converts value to specified data type and if conversion fails it returns NULL. For example, source value in string format and we need it in double/integer format. Then this will help us in achieving it. Syntax: TRY_CAST ( expression AS data_type [ ( length ) ] ) TRY_CAST() retu...

Convert

When you convert expressions from one type to another, in many cases there will be a need within a stored procedure or other routine to convert data from a datetime type to a varchar type. The Convert function is used for such things. The CONVERT() function can be used to display date/time data in v...

RESTful API Routes for Multi Module Application

// Define new router group $api = new \Phalcon\Mvc\Router\Group([ 'module' => 'api', ]); $api->setPrefix('/api/v1'); // API routes (Maps to Cotnroller::Action) $api->addGet('/users', 'Users::index'); $api->addGet('/users/search/{query}', 'Users::search'); $api->addGet('/...

Page 526 of 1016

logo rip
SUPPORT & PARTNERS
STAY CONNECTED

Get monthly updates about new articles, cheatsheets, and tricks.