Comparing Two SQL-Based Approaches for Querying JSON: SQL++









Module Title

JSON documents in tables and queries JSON documents through a JSON query language. It currently supports SQL Server and MariaDB as the underlying database.
JsonServer User Manual


Native JSON Datatype Support: Maturing SQL and NoSQL

Microsoft SQL Server [10] MySQL [12]
p liu


SQL Server Advanced Data Types: JSON XML

http://projanco.com/Library/SQL%20Server%20Advanced%20Data%20Types.%20JSON


Big Data Management System

20 oct. 2016 Growing influence on server side coding (Node.js) ... Example JSON document ... Full query capabilities using JSON Path XQuery and SQL.
cr json technical overview





TIBCO Flogo® Enterprise Activities Triggers

https://docs.tibco.com/pub/flogo/2.13.0/doc/pdf/TIB_flogo_2.13.0_activities_triggers_and_connections_guide.pdf?id=2


Querying JSON with Oracle Database 12c Release 2

Complex Queries on JSON content using SQL/JSON. 11. Relational access to JSON content In the example above the JSON represents a Purchase Order object.
sql json wp


Comparing Two SQL-Based Approaches for Querying JSON: SQL++

The SQL++ example queries that you are about to read are written in N1QL and have run successfully on Couchbase Server and (with minor syntactic variations) 
Comparing Two SQL Based Approaches WP


EEA Discodata

30 records See the Discodata JSON for more information. ... various WISE SOE database queries in the SQL query examples to get an idea on how the SQL works.





microsoft-sql-server.pdf

3 mars 2011 Chapter 49: JSON in Sql Server. 146. Syntax. 146. Parameters. 146. Remarks. 146. Examples. 146. Format Query Results as JSON with FOR JSON.
microsoft sql server


Microsoft SQL Server 2019 to Amazon Aurora MySQL Migration

SQL Server to Aurora MySQL Migration Playbook: Microsoft SQL Server Unused CTE query definition. ... For more information see JSON and XML (p. 165).
dms mpb sql server to aurora mysql


213600 Comparing Two SQL-Based Approaches for Querying JSON: SQL

Comparing Two SQL-Based Approaches

for Querying JSON: SQL++ and SQL:2016

Don Chamberlin 08/2019

Introduction

According to GitHub's Octoverse 2018 report, JavaScript is the most widely-used programming 1

The Languages

SQL++ 2

SQL:2016

SQL/JSON Query Functions

true true false {"persons": [ { "id": 101, "name": "Bill", "location": "Chicago", "skills": [ { "skill": "welding", "level": 5}, { "skill": "cooking", "level": 2} "hobbies": [ { "hobby": "bird watching", "years": 3 }, { "hobby": "stamp collecting", "years": 4} persons ( id, name, location ) skills ( id, skill, level ) hobbies ( id, hobby, years )

Path Language

persons [ * ] ? (@.location == "Chicago") . hobbies [ * ] ? (@.years >= 3) . hobby persons ? (@.location == "Chicago") . hobbies hobbies member, which in this path is another ? (@.years >= 3) . hobby two sets, returning true 5

SQL/JSON Constructor Functions

Examples

sales stateunits

SELECT units

FROM sales

WHERE state = 'CA';

[ { "state": "CA", "units": 7500 }, { "state": "NY", "units": 4000 } ] units

SELECT units

FROM sales

WHERE state = 'CA';

SELECT s.units

FROM sales AS s

WHERE s.state = 'CA';

sales as

representing the table, and the alias s as representing one row of the table as the query iterates over

[ { "units": 7500 } ] sales data

SELECT JSON_VALUE(s.data,

'lax $[ * ] ? (@.state == "CA").units'

RETURNING INTEGER)

FROM sales AS s;

s.data data lax ? ( @.state == "CA" ) .units units

SELECT JSON_OBJECT ('units':

JSON_VALUE(s.data,

'lax $[ * ] ? (@.state == "CA").units'

RETURNING INTEGER))

FROM sales AS s;

{ "units": 7500 } Suppose that you are an economist, and that each year you receive, from some research agency, a 9 [ { "year": 2016, "units_sold": [ { "brand": "Ford", "car_sales": [ { "state": "CA", "units": 7500 }, { "state": "NY", "units": 4000 } "truck_sales": [ { "state": "CA", "units": 2800 }, { "state": "NY", "units": 4700 } { "brand": "Honda", "car_sales": [ { "state": "CA", "units": 3500 }, { "state": "NY", "units": 5200 } "truck_sales": [ { "state": "CA", "units": 2100 }, { "state": "NY", "units": 1900 } { "year": 2017, "units_sold": [...] above as a table named vehicle_sales , containing a nested table named units_sold , which in turn contains two more nested tables named car_sales and truck_sales vehicle_sales 10 vehicle_sales yearunits_sold brandcar_salestruck_sales vehicle_sales

Big-Car-Sales

year units car_sales brand units_sold state car_sales

FROM vehicle_sales AS vs

UNNEST vs.units_sold AS us

UNNEST us.car_sales AS cs

vs vehicle_sales. us units_sold vs. cs car_sales

SELECT us.brand, cs.state, cs.units AS cars

FROM vehicle_sales AS vs

UNNEST vs.units_sold AS us

UNNEST us.car_sales AS cs

WHERE vs.year = 2016 AND cs.units >= 5000

ORDER BY cars DESC;

FROM vehicle_sales AS vs

UNNEST vs.units_sold AS us

UNNEST us.car_sales AS cs

WHERE vs.year = 2016 AND cs.units >= 5000

SELECT us.brand, cs.state, cs.units AS cars

ORDER BY cars DESC;

Comparing Two SQL-Based Approaches

for Querying JSON: SQL++ and SQL:2016

Don Chamberlin 08/2019

Introduction

According to GitHub's Octoverse 2018 report, JavaScript is the most widely-used programming 1

The Languages

SQL++ 2

SQL:2016

SQL/JSON Query Functions

true true false {"persons": [ { "id": 101, "name": "Bill", "location": "Chicago", "skills": [ { "skill": "welding", "level": 5}, { "skill": "cooking", "level": 2} "hobbies": [ { "hobby": "bird watching", "years": 3 }, { "hobby": "stamp collecting", "years": 4} persons ( id, name, location ) skills ( id, skill, level ) hobbies ( id, hobby, years )

Path Language

persons [ * ] ? (@.location == "Chicago") . hobbies [ * ] ? (@.years >= 3) . hobby persons ? (@.location == "Chicago") . hobbies hobbies member, which in this path is another ? (@.years >= 3) . hobby two sets, returning true 5

SQL/JSON Constructor Functions

Examples

sales stateunits

SELECT units

FROM sales

WHERE state = 'CA';

[ { "state": "CA", "units": 7500 }, { "state": "NY", "units": 4000 } ] units

SELECT units

FROM sales

WHERE state = 'CA';

SELECT s.units

FROM sales AS s

WHERE s.state = 'CA';

sales as

representing the table, and the alias s as representing one row of the table as the query iterates over

[ { "units": 7500 } ] sales data

SELECT JSON_VALUE(s.data,

'lax $[ * ] ? (@.state == "CA").units'

RETURNING INTEGER)

FROM sales AS s;

s.data data lax ? ( @.state == "CA" ) .units units

SELECT JSON_OBJECT ('units':

JSON_VALUE(s.data,

'lax $[ * ] ? (@.state == "CA").units'

RETURNING INTEGER))

FROM sales AS s;

{ "units": 7500 } Suppose that you are an economist, and that each year you receive, from some research agency, a 9 [ { "year": 2016, "units_sold": [ { "brand": "Ford", "car_sales": [ { "state": "CA", "units": 7500 }, { "state": "NY", "units": 4000 } "truck_sales": [ { "state": "CA", "units": 2800 }, { "state": "NY", "units": 4700 } { "brand": "Honda", "car_sales": [ { "state": "CA", "units": 3500 }, { "state": "NY", "units": 5200 } "truck_sales": [ { "state": "CA", "units": 2100 }, { "state": "NY", "units": 1900 } { "year": 2017, "units_sold": [...] above as a table named vehicle_sales , containing a nested table named units_sold , which in turn contains two more nested tables named car_sales and truck_sales vehicle_sales 10 vehicle_sales yearunits_sold brandcar_salestruck_sales vehicle_sales

Big-Car-Sales

year units car_sales brand units_sold state car_sales

FROM vehicle_sales AS vs

UNNEST vs.units_sold AS us

UNNEST us.car_sales AS cs

vs vehicle_sales. us units_sold vs. cs car_sales

SELECT us.brand, cs.state, cs.units AS cars

FROM vehicle_sales AS vs

UNNEST vs.units_sold AS us

UNNEST us.car_sales AS cs

WHERE vs.year = 2016 AND cs.units >= 5000

ORDER BY cars DESC;

FROM vehicle_sales AS vs

UNNEST vs.units_sold AS us

UNNEST us.car_sales AS cs

WHERE vs.year = 2016 AND cs.units >= 5000

SELECT us.brand, cs.state, cs.units AS cars

ORDER BY cars DESC;


12345 Next