SQL JSON Query

The JSON format, also known as JavaScript Object Notation, is a lightweight data transfer format. It's a common file format built from JavaScript that's used to store data as key/attribute and value pairs or array data types in a serializable way. It is a widely used data type because it is simple to read and understand by most people.

Nowadays, JSON is a widely used data transfer format. The majority of current services return data in JSON format. It is mostly used for data storage and transmission. JSON format data is used by almost all NoSQL databases, including MongoDB, CouchDB, and others. JSON is the ideal format for transferring data from one server to a web page since front-end programs such as Android, iOS, React, and Angular, among others, can interpret JSON contents and show them as needed. We can transfer JSON information and store it easily in rows, even in SQL.

JSON is most commonly used to transfer data between a server and a web application. The JSON file format, on the other hand, has several drawbacks. Because it is human readable and simple to parse, it may compromise the database's overall security.

Applications of JSON Data Type

It's language-independent, simple to comprehend, and self-descriptive. As a result, it works on a variety of platforms and in a variety of languages.

JSON is compatible with a wide range of browsers and operating systems. As a result, making JSON-based applications accessible to a wide number of browsers does not require much effort.

It is lightweight, which helps the program's or platform's overall performance.

It is used as a replacement for XML. Other file formats, such as CSV and XML, are slower.

Cross feature compatibility

All SQL server components, such as Hekaton, temporal, and column storage tables, allow the data type NVARCHAR. If we believe JSON is compatible with SQL Server's X feature, the easy answer is that if NVARCHAR is compatible with X feature, JSON will be as well.

Migration

JSON was saved to the database as text. If the JSON type was introduced, they would have to alter the database schema and move the data to the new functionality.

Client-side support

On the client side, there is presently no standardised JSON object type, such as the XmlDom object.

JSON in-built functions :

• ISJSON
• JSON_VALUE
• JSON_QUERY
• JSON_MODIFY
• OPENJSON
• FOR JSON

Syntax and Parameters:

A standard JSON data is written as key/attribute and value pairs. The syntax is something like this :

{“key” : “value”}

A key value pair starts with a "field name or attribute," then a colon, and then the attribute's value. Double quotations are used for both the field name and the value.

Multiple records are written together within a set of square brackets, with each record separated by a comma and written with a set of curly braces. The following is the common syntax :

{
column_name1":"value_a1",
"column_name2":"value_a2",
"column_name3":"value_a3",
"column_name4":"value_a4"
},{
"column_name1":"value_b1",
"column_name2":"value_b2",
"column_name3":"value_b3",
"column_name4":"value_b4"
}

Example 1: Let's use SQL Server Azure Data Studio. Let's also set up a database and a table. Then you can go on to JSON. Azure Data Studio is compatible with Windows 10, Mac OS X, and Linux. It is possible to install it from this location.

Database creation :

Command to create the database. Here GEEKSFORGEEKS is the db name.

CREATE DATABASE GEEKSFORGEEKS;

To make the database active use the below command:

USE GEEKSFORGEEKS;

Example 2: PostgreSQL has supported the native JSON data type. It has a lot of functions and operators for working with JSON data.

CREATE TABLE orders (
id serial NOT NULL PRIMARY KEY,
info json NOT NULL
);

Example 1: array into rows:

select id, jsonb_array_elements(commodity.detail->'data') from commodity;

Output:

Example 2: Json Array to Rows using Lateral Join

I have two JSON Arrays in my table's details column, and I need to assess the query in the same way that I do in another relational table.

{
    "city": "London",
    "name": "Sainburry",
    "quantities": [112, 145, 222, 122, 124],
    "prices": [4, 4, 4, 0, 3],
    "dates": ["13.05.2020", "14.05.2020", "15.05.2020", "16.05.2020", "17.05.2020"]
}

I want to evaluate the following query for this JSON Array:

select quantities,
prices,
AVG(quantities/prices::float) as ratio
from my_table
where city = 'London'
group by quantities, prices;

Example 1: Query JSON column in MySQL:

Consider the table users(id, details), in which id is an integer and the primary key, and details is a JSON data type column.

create table users(
id int auto_increment primary key,
details json
   );

We will insert the following JSON data in our table.

insert into users(details)
 values(
 '{ "page": "/" , 
 "name": "Safari", 
 "os": "Mac", 
 "spend": [100, 50]
 "resolution": { "x": 1920, "y": 1080 } }'
),
(
 '{ "page": "/products", 
 "name": "Chrome", 
 "os": "Windows", 
 "spend": [150, 250]
 "resolution": { "x": 1680, "y": 1050 } }'
),
(
 '{ "page": "/shoes", 
 "name": "Firefox", 
 "os": "Windows", 
 "spend": [200,300]
 "resolution": { "x": 1280, "y": 800 } }'
);

Example 2: Create a variable Because the FOR JSON clause produces data of type NVARCHAR(MAX), you can allocate it to any variable, as illustrated in the scenario below.

DECLARE @SalesOrder NVARCHAR(MAX) = (SELECT TOP 10 * FROM Sales.SalesOrderHeader FOR JSON AUTO)
Then select from @SalesOrder

Simply add the FOR JSON AUTO clause to the conclusion of your SELECT query to convert your data to JSON format. The sequence of columns in your SELECT query, as well as the tables specified by the SELECT statement, influence the format of the JSON output in this mode.

JSON AUTO would handle this quickly, but JSON assistance is only available in SQL Server 2016 and later, particularly in Azure SQL Database. You must create the JSON string yourself for a T-SQL solution in SQL 2014 and before.

Example 1: The example below utilizes a FOR XML subquery to concatenate the output into JSON format and inserts the array's outermost [and]. Notice that this does not handle characters that must be avoided in JSON ("and"), so if you have these in your data, you'll want to use REPLACE to escape them.

SELECT '[' + STUFF((
 SELECT
  ',{'
  + '"Id":'+CAST(Id AS varchar(10)) + ','
  + COALESCE('"Name":"' + Name + '",','')
  + COALESCE('"About":"' + About + '",','')
  + COALESCE('"Age":'+CAST(Age AS varchar(10)) + ',','')
  + COALESCE('"AddressId":'+CAST(AddressId AS varchar(10)), '')
  + '}'
  FROM TestTable
  FOR XML PATH(''), TYPE).value('.', 'varchar(MAX)'),1,1,'')
  + ']';

Example 2: The results of our artificial Fruit Sales data mart are converted to JSON using a SQL query.

SELECT sales.[Item Nr],
sales.[Transaction Date],sales.[Fruit],sales.[Quantity]
,sales.[Customer],sales.[MOP],sales.[Account Number]
FROM [selectSIFISOBlogs].[DIM].[FruitSales] sales
FOR JSON AUTO

Example 3: With a colon, SQL Server can recognize the time zone. The following is how you should format the timezone part:

SELECT CONVERT(datetime2, STUFF('2009-06-18T16:44:20+0000', 23, 0, ':'))

According to MSDN ISO 8601 has YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm] format.

Before you can conduct an INSERT operation, you must first construct a table for the JSON data. The table's columns, on the other hand, must correspond to the JSON data's keys.

Specify BLOB as the data type for the column that will contain JSON data in the CREATE TABLE SQL query.

Example 1: Assume the JSON data uses the following keys in a JavaScript array:

["id", "str_col", "int_col", "bool_col", "json_col", "float_col"];

In this case, the CREATE TABLE SQL statement should resemble the following:

CREATE TABLE json_table (
 id VARCHAR(50) PRIMARY KEY,
 str_col VARCHAR(500),
 int_col SMALLINT,
 bool_col BOOLEAN,
 json_col JSON,
 float_col DECIMAL
);

Example 2: creates a table with a JSON column that is non-LOB.

CREATE TABLE jsonTable1(id INTEGER,
jsn1 JSON(64000) CHARACTER SET LATIN);

This example creates a table with non-LOB and LOB JASON columns.

CREATE TABLE jsonTable2(id INTEGER,
jsn1 JSON(1000) CHARACTER SET LATIN,
jsn2 JSON INLINE LENGTH 30000 CHARACTER SET LATIN);

Example 3: create a table with json column.

CREATE TABLE `book` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `tags` json DEFAULT NULL,  // line 4
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

When I try to modify the column type in a current table's Structure tab, there is no such choice in the list.

Example 4: To save JSON data, the illustration generates a CALL_RECORDS column.

CREATE TABLE CLAIMS (CLAIM_ID       VARCHAR(10),
POLICY_NUM     VARCHAR(12),
CUSTOMER_ID    VARCHAR(8),
STATUS         VARCHAR(20),
CALL_RECORDS   BLOB);

Because the source data contains special characters '\', the FOR JSON clause escapes them, as seen in the table. This escape happens in both the names and values of properties.

ESCAPING OF SPECIAL CHARACTERS

Characters that are retained in JSON must be appropriately escaped before being utilised in strings.

In a JSON string, the JSONObject.escape() method can be used to escape reserved terms.

Example 1: This is an instance of FOR JSON output for source data that contains both special and control characters.

SELECT  
  'VALUE\    /  
  "' as [KEY\/"],  
  CHAR(0) as '0',  
  CHAR(1) as '1',  
  CHAR(31) as '31'  
FOR JSON PATH  

Output:

Example 2:

import org.json.simple.JSONObject;

public class JsonDemo {
 public static void main(String[] args) {
  JSONObject jsonObject = new JSONObject();
  String text = "Text with special character /\"\'\b\f\t\r\n.";
  System.out.println(text);
  System.out.println("After escaping.");
  text = jsonObject.escape(text);
  System.out.println(text);
   }
}

Output:

Example 3:

SELECT STRING_ESCAPE('' , 'json');
Which gives (simply)

So when we see a JSON document like this

The SQL/JSON condition json_exists enables you to pick rows based on the content of JSON documents using a SQL/JSON path expression as a row filter. In a CASE expression or the WHERE clause of a SELECT statement, you can utilize the JSON exists condition.

The json_exists condition tests for the presence of a specific value in JSON data, returning true if the value is present and false otherwise. JSON exists returns true if the data it is looking for satisfies one or more JSON values. It returns false if no JSON values are found to match.

JSON_EXISTS determines whether or not a given JSON path exists in JSON data.

Starting with '$', the JSON route is analyzed from the left, with zero or more steps: each step might be an object or an array. JSON_ EXISTS returns TRUE if a match is discovered after parsing the entire route; otherwise, it returns FALSE.

Example 1: The JSON_EXISTS predicate can be used to see if a JSON path expression returns a value in a JSON document (also see the JSON_VALUE function) :

SELECT 1
FROM dual
WHERE json_exists('{"a":1}', '$.a')

Example 2: In the CUSTOMER table’s data, let’s look for the path to "OfficePh" in the JSON column.

JSON_EXISTS: Select rows where the JSON

document contains an OfficePh attribute

select * from customer
where json_exists
(metadata, '$.OfficePh');

Example 3: selects purchase-order documents that have a line item whose part description contains a UPC code entry.

SELECT po.po_document FROM j_purchaseorder po
WHERE json_exists(po.po_document, '$.LineItems.Part.UPCCode');

This function takes a suitable JSON string and retrieves one or more SQL values. JSON EXTRACT requires a JSON path expression that describes the value to extract for each SQL value.

JSON_EXTRACT allows you to specify an unlimited amount of json path expr options. The EMITS clause for each json_path_expr must describe a column name and a data type for the JSON values it delivers. The EMITS clause's columns are arranged in the same order as the json_path_expr arguments. As a result, the first json path expr has the first column_name and data_type.

JSON_EXTRACT gives an error if any json_path_expr fails to identify a SQL value. As a result, the default for each json_path expr is ERROR ON EMPTY.

JSON EXTRACT throws an exception by default if any error happens during the JSON processing. As a result, the default for each json_path_expr is ERROR ON ERROR.

You should be aware of the concept of a route expression if you're going to pick rows using a JSON field. The target object keys and a dollar sign symbol ($) are used in path expressions.

You can retrieve the values for the selected column using the JSON_EXTRACT function in conjunction with it.

Example 1:

CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"firstname" : "Ann", "surname" : "Smith", "age" : 29}');
INSERT INTO json_input VALUES(2, '{"firstname" : "Sue", "surname" : "Smith", "age" : 42}');
INSERT INTO json_input VALUES(3, '{"firstname" : "Claire", "surname" : "Doe", "age" : "Five"}');
INSERT INTO json_input VALUES(4, '{"firstname" : "John", "name" : "Doe", "age" : 50}');

SELECT id,
 JSON_EXTRACT (json,
  '$.firstname', 
  '$.surname', 
  '$.age', 
  '$.error()'
  )
 EMITS(
  forename VARCHAR(100), 
  surname VARCHAR(100), 
  age INT,
  error_column VARCHAR(2000000)
  )
FROM json_input;

Example 2: Consider a scenario where you are interested in all of the televisions that have at least one USB and one HDMI port:

SELECT *
FROM
	`e_store`.`products`
WHERE
	`category_id` = 1
AND JSON_EXTRACT(`attributes` , '$.ports.usb') > 0
AND JSON_EXTRACT(`attributes` , '$.ports.hdmi') > 0;

The first argument to the JSON_EXTRACT function is the JSON to apply the path expression to which is the attributes column. The $ symbol tokenizes the object to work with. The $.ports.usb and $.ports.hdmi path expressions translate to “take the usb key under ports” and “take the hdmi key under ports” respectively.

Example 3: Count a certain attribute in the json string and do some operations on it, such as sum avg :

SELECT JSON_EXTRACT (field name,'$.attribute name') FROM XXX WHERE
XXX
//json array
 SELECT JSON_EXTRACT (Field name,'$[0]. Property name') FROM XXX WHERE
XX

The OPENROWSET function reads data from files stored on the file system.

Create a single column from a JSON document. If SQL Server has read access to the file, OPENROWSET(BULK) is a table-valued function that can read data from any file on the local drive or network. It outputs a table with a single column containing the file's contents.

The "BulkColumn" can be used to read the actual contents of the JSON file, which can then be saved in a variable. Before attempting to use JSON data, it is usually a good idea to validate it with the ISJSON function. If the JSON format is suitable, this function will return 1.

It is a table-valued function capable of reading data from any file. It returns a table with a single column containing all of the file's contents. It can just load a file's whole contents as a text value. (A single character large object, or SINGLE_CLOB, is the name for this single huge item.)

Syntax:

SELECT * FROM OPENROWSET (BULK 'file_path', SINGLE_CLOB) as correlation_name;

Example 1: The script below uses the "BulkColumn" variable to read the Orders.JSON file and store its contents in a variable. This script will also use the ISJSON method to validate the JSON.

Declare @JSON varchar(max)

SELECT @JSON = BulkColumn
FROM OPENROWSET (BULK 'E:\Temp\Data\Orders.JSON', SINGLE_CLOB) as j

Select @JSON

If (ISJSON(@JSON)=1)
Print 'Valid JSON'

Example 2: It retrieves the file's content and stores it in BulkColumn. There must be a term for the correlation. "file1.json" is the name of the JSON file we have. JSON document content:

[{"Firstname": "ROMY", "Lastname": "KUMARI", "Gender": "female", "AGE" : 22 },
{"Firstname": "PUSHKAR", "Lastname": "JHA", "Gender": "male", "AGE" : 22 },
{"Firstname": "SHALINI", "Lastname": "JHA", "Gender": "female", "AGE" : 21 },
{"Firstname": "SAMBHAVI", "Lastname": "JHA", "Gender": "female", "AGE" : 18 } ]

Example 3: The below query will read the JSON file contents in the specified file path.

SELECT * 
FROM OPENROWSET (BULK 'C:\sample-json-file.json', SINGLE_CLOB) as JsonFile

Example 4:

Select BulkColumn from openrowset(Bulk'D:\home\HS\HS-Web\wwwroot\Json files\test.json',single_blob)JSON;

--View the imported data from Bulk Import as a single column

DECLARE @TestDetails VARCHAR(MAX)
SELECT @TestDetails = BulkColumn FROM 
OPENROWSET(BULK'D:\Omkar\Projects\HS\Documents\test.json', SINGLE_BLOB) JSON;

SELECT @TestDetails as SingleRow_Column

To validate or update JSON text, or to extract simple or complicated information, use the functions provided on the pages in this article.

JSON FUNCTIONS

Functions and Description

There are three functions and three conditions that all deal with JSON.

• json_value – A scalar value is extracted from a JSON string.
• json_query – From a JSON string, creates an object or an array. (This might be an array).
• json_table – A function that generates table logic from a json file.
• is [not] json – This function determines whether a string includes valid JSON.
• json_exists – examines the json document for the presence of a specific element or structure.
• json_textcontains – Using an Oracle text index, efficiently search across a json document.
• json_modify – Returns the updated JSON string after changing the value of a property in a JSON string.

Data from JSON can now be stored in the new Postgres table. Before putting an item into the table, use the Postgres json_populate_record() function to populate it with JSON data.

Example 1: On creating table set your field as JSON datatype.

INSERT INTO person VALUES (‘{“pid”: 101, “name”: “name1”}’); 

INSERT INTO person VALUES (‘{“pid”: 102, “name”: “name2”}’); 

Select JSON data, SELECT * FROM `person` WHERE JSON_CONTAINS(name, ‘[“name1”]’);

Example 2: Insert a JSON document into sql

The following example demonstrates how to insert JSON data into a NULL object as Postgres records so that it may be inserted into a table:

INSERT INTO json_table
SELECT id, str_col, int_col, bool_col, json_col, float_col
FROM json_populate_record (NULL::json_table,
 '{
   "id": "0",
   "str_col": "orkb type foo examples tutorials orkb",
   "int_col": 5743,
   "bool_col": true,
   "json_col": {
    "ObjectRocket": "examples"
  },
   "float_col": 94.55681687716474
  }'
);

NOTE: In the SQL query above, the double colons (::) represent a data conversion type. In this scenario, it uses the json_populate_record() function to turn a NULL object into data that the Postgres table can digest.

To filter the returned rows, we can utilize the JSON operators in the WHERE clause.

Example 1: Query with a where clause in mysql json table

SELECT *
FROM t1
WHERE json_extract(jdoc, '$.key1')='value1'

Example 2: Let’s imagine we want to return records for anyone in who is living in America:

SELECT about ->> 'name' AS name
FROM demo WHERE about -> 'info' ->> 'address' = 'America';

Output:

Example 3: Use JSON_VALUE:

SELECT t.*
FROM tableA t
WHERE JSON_VALUE(col3, '$.key') LIKE 'some_value'

This assumes that the column which contains the JSON value {'key':'value'} is called col3.

Example 4: To find out who bought Diaper, we use the following query:

SELECT info ->> 'customer' AS customer
FROM orders
WHERE info -> 'items' ->> 'product' = 'Diaper';

To find out who bought two products at a time, we use the following query:

SELECT info ->> 'customer' AS customer,
info -> 'items' ->> 'product' AS product
FROM orders
WHERE CAST ( info -> 'items' ->> 'qty' AS INTEGER) = 2

We used the type cast to transform the quantity field into an INTEGER type and compare it to two.

You can edit a current property value, delete a property value from a JSON string, delete a property, and add a new Key and its value to the JSON string using the JSON MODIFY function.

When the string is not incorrect in JSON format, it returns an error.

In the operational database, data alteration is an obligatory duty. It assists in the system's data cleansing. Change tracking, on the other hand, will be discussed in another blog article.

The syntax goes like this:

JSON_MODIFY ( expression , path , newValue )

expression = A valid JSON value

path = A JSON path expression that specifies the property to update.

< path Expression >

[append] [ lax | strict ] $.< json path>

newValue = The new value must be a [n]varchar or text.

If the new value is NULL, JSON_MODIFY deletes the given key in lax mode.

This function returns the revised result of expression as JSON text that has been appropriately formatted.

Example 1:

declare @weather as nvarchar(500)
set @weather = N'{
“main”: {
“temp”: 282.55,
“feels_like”: 281.86,
“temp_min”: 280.37,
“temp_max”: 284.26,
“pressure”: 1023,
“humidity”: 100
}
}’

print ‘Is valid JSON : ‘ + str( ISJSON(@weather) )

SET @weather=JSON_MODIFY(@weather,’$.main.temp’, 300.23)
print ‘Modified value’
print @weather

Output:

Example 2: Here’s an example to demonstrate.

SELECT JSON_MODIFY('{"Name": "Homer"}', '$.Name', 'Morgan') AS 'Result';

Result:

In this example:

{"Name": "Homer"}

the original JSON string

$.Name is the path (this begins with $. followed by the path to the property we want to update).

Morgan is the new value we want to assign to Name (i.e. to replace the current value).

Example 3: example of using JSON_MODIFY function in SQL Server.

Here we have a simple JSON string as shown Below.

{"Item":"Iphone 5S", "Price":25000}

Example 1: sort on a JSON Path

SELECT id, name, age, 
meta FROM users ORDER BY meta ->> ? DESC
func (User) Fields() []ent.Field {
return []ent.Field{
field.String("name"),
field.Int("age"),
field.JSON("meta", map[string]string{}),
	}
}
client.User.Query().Order(func(s *sql.Selector, check func(string) bool) {
  s.OrderBy(...)
})

Example 2: JSON fields in the regular queries, e.g.:

SELECT * FROM mytab
WHERE JSON_VALUE(json_col, '$.lastname') LIKE '%Connor%'
ORDER BY JSON_VALUE(json_col, '$.firstname') 

You can even design property indexes. More examples can be found on MSDN. This functionality will also be accessible in Azure SQL Database. You'll need to utilise a CLR assembly that parses JSON in earlier versions.

The OPENJSON function parses JSON text and delivers a number of results. The paths indicated in the WITH clause are used to specify the values that should be returned. If no path is supplied for a column, the name of the column is utilised as a path. This function converts returned values to SQL types specified by the WITH clause. If an object or array is to be returned, the AS JSON choice must be given in the column definition.

Parse JSON in SQL Server using a free utility that can be downloaded from the internet and installed with SSIS.

When used with a WITH clause, OPENJSON provides a beautiful, tidy, and type-safe(!) way to read your JSON. I'd go with BIT because true and false will be implicitly translated.

Example 1: Parse JSON File:

SELECT t.RelatedPolimorphicId
 ,t.[Key] 
 ,A.*
FROM YourMetaDataTable t
CROSS APPLY OPENJSON(t.[Value]) 
WITH (
 BrandPresent BIT
 ,OneImage BIT
 ,UPCPresenet BIT
 ,ModelNumberPresent BIT
 ,TitlePresent BIT
 ,DescriptionPresent BIT
  ,Feature1Present BIT
) A;

Example 2: In this new example, we will show how to parse JSON values in SQL Server.

First of all, will be to create a simple table in SQL Server to store JSON values:

CREATE TABLE [dbo].[book](
[author] [nchar](50) NULL,
[isbn] [nchar](20) NULL,
[price] [decimal](18, 0) NULL,
[category] [nchar](20) NULL,
[id] [smallint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_book] PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Example 3:

DECLARE @json NVARCHAR(100) = '{"id": 1, "user":{"name":"John"}, "skills":["C#","SQL"]}'
SELECT * 
FROM OPENJSON (@json)
WITH(Id int '$.id',
Name nvarchar(100) '$.user.name',
UserObject nvarchar(max) '$.user' AS JSON,
Skills nvarchar(max) '$.skills' AS JSON,
Skill0 nvarchar(20) '$.skills[0]')

Output:

Using a SQL/JSON path expression, JSON VALUE() and JSON MODIFY() extract JSON content from a JSON context object.

The JSON function SQL JSON_QUERY retrieves an object or an array from a JSON string.

The JSON_QUERY function cannot retrieve a scalar value. JSON object or JSON array is always extracted. The JSON_VALUE function can be used to extract a scalar value.

Syntax :

JSON_QUERY ( json_string ,[path mode] json_path )

The json_string is contained in the string json string.

json_path is the JSON string's path, which defines the path to the JSON Object or Array to extract in the json_string.

[path mode]: It is an optional, It can be specified with json_path, it can be either lax or strict.

If the supplied json path is not present in the json_string, path_mode returns null; however, if path_mode is strict, it always raises an exception.

Example 1: Lets extracts the color array using the JSON_QUERY function.

DECLARE @string_Json NVARCHAR(150)
 = '{"Item":"Iphone 5S",
 "Price":25000,
 "Color":["Black", "Grey", "Gold"]}'
SELECT JSON_QUERY(@string_Json,'$.Color') AS JSON_Color

Example 2: The following query returns the value {"item1":1,"item2":2,"item3":3}.

SELECT JSON_QUERY('{"item1":1, "item2":2, "item3":3}', '$') AS JSONQUERY FROM DUMMY;

Example 3: Get the JSON object from a JSON string

In this example, we require to retrieve the first JSON object from the [employees] key.

A variable @data contains an array for the “employees” key

We can note the array is enclosed in a square bracket

JSON array follows zero-based indexing. To retrieve the first record, we use employees[0] argument

Similarly, we can access the second record using the employees[1] argument

DECLARE @data NVARCHAR(4000);
SET @data = N'{
"employees":
[      {
  "name":"Raj",
  "email":"raj@gmail.com",
  "age":32 
},
  {
   "name":"Mohan",
   "email":"Mohan@yahoo.com",
   "age":21    
  }   
]
}';
SELECT JSON_QUERY(@data, '$.employees[0]') AS 'Result';

JSON_VALUE is a JSON function for extracting scalar values from a JSON string. Keep in mind to extract the object and array using JSON_ Query.

It gives you a single nvarchar text value.

You must pass two arguments to this function: the JSON expression and the property to extract.

It is not possible to extract a JSON object or JSON array from a JSON string; instead, use the JSON_QUERY function.

Syntax:

JSON_VALUE ( json_string , [path mode] json_path )

The json_string is contained in the string json string.

json_path is the JSON string's path, which defines the path to the JSON Object or Array to extract in the json_string.

[path mode]: It is an optional, It can be specified with json_path, it can be either lax or strict.

If the supplied json path is not present in the json_string, path_mode returns null; however, if path_mode is strict, it always raises an exception.

If a supplied expression or path is invalid, or if the value returned exceeds 4000 characters, we can expect an error.

Example 1: Lets extract the value of Item property from following JSON string.

DECLARE @string_Json NVARCHAR(MAX)
= '{
"Item":"Iphone 5S",
"Price":25000,
"Color":["Black", "Grey", "Gold"]
}'

SELECT JSON_VALUE(@string_Json,'$.Item') AS JSON_Color

Example 2:

/*A JSON object */

declare @color as nvarchar(500);
set @color = N'{
“color”: “red”,
“value”: “#f00”
}’

print ‘Is my JSON valid : ‘ +str( isjson(@color))

/* get value of color key */
print ‘color is ‘ + json_value(@color, ‘$.color’)
print ‘value is ‘ + json_value(@color, ‘$.value’)

Output:

Example 3: Here’s an example to demonstrate basic usage of the JSON_VALUE() function.

SELECT JSON_VALUE('{"Name": "Marcus"}', '$.Name') AS 'Result';

Result:

In this example:

The {"Name": "Marcus"} argument is the JSON expression (a small one, but still a valid JSON expression). JSON expressions consist of a key/value pair. In this case, Name is the key, Bruce is its value.

The $.Name argument is the path. This path references the value of the Name key of the JSON expression. So we can extract the value by referencing the name of the pair.

Example 4: The following query returns the value of the member with property name a. Because the RETURNING clause is not specified, the value is returned as a VARCHAR2(4000) data type:

SELECT JSON_VALUE('{a:100}', '$.a') AS value
FROM DUAL;

Output: