JSON + Relational – How to Use Hybrid Data Models

Is it possible to achieve schema flexibility without sacrificing transactions or data integrity? Yes – by extending relational data with JSON documents! You can index and query data regardless of whether it’s structured, semi-structured, or both.

Join this webinar to learn how to use hybrid data models with MariaDB. You’ll see how to enforce data integrity when creating and updating JSON documents, and how to query them with SQL.

That’s not all. With a live demonstration of a web application built with ReactJS and Node.js, integrating the Node.js connector, you will see how to convert relational into JSON and how to merge relational with JSON – all via SQL.

1. JSON + Relational
How to use hybrid data models
Rob Hedgpeth, Developer Evangelist

MariaDB Corporation

2. About me
I’m not a morning person,
but I like breakfast.

3. What’s the point?

4. What’s the point?

5. About me
I’m not a NoSQL person,
but I like JSON.

6. LAYOUT
Comparison (Dark/Blue)
JSON
Relational
Flexibility
Simplicity
Ubiquity
Data integrity
Transactions
Reliability

7. What’s the difference?
Structured data is externally described
Semi-structured data is self described

8. Structured or semi-structured data

9. Structured or semi-structured data

Sports venues Attractions Restaurants

10. A data model can be comprised of
structured and semi-structured data.

11. Structured and semi-structured data
with relational + JSON
Structured Semi-structured
name latitude longitude attr
Lou Malnati’s 42.0021628 -87.7255662
{ “foodType”: “Pizza”, “menu”: “loumalnatis.com/our-menu” }

12. name latitude longitude attr
Lou Malnati’s 42.0021628 -87.7255662
{ “foodType”: “Pizza”, “menu”: “loumalnatis.com/our-menu” }
Structured and semi-structured data
with relational + JSON
Structure data described
by the schema
Structured described
by the data

13. Creating and querying
JSON documents

14. Definition
CREATE TABLE locations (
id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name varchar(100) NOT NULL,
description varchar(500),
type char(1) NOT NULL,
latitude decimal(9, 4) NOT NULL,
longitude decimal(9, 4) NOT NULL,
attr longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
CHECK(json_valid(attr)),
PRIMARY KEY (id))
ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4;

15. Creating Records
INSERT INTO locations (type, name, latitude, longitude, attr) VALUES
('R', 'Lou Malnatis', 42.0021628, -87.7255662,'{"details": {"foodType": "Pizza", "menu":
"https://www.loumalnatis.com/our-menu"},
"favorites": [{"description": "Pepperoni deep dish", "price": 18.75}, {"description":
"The Lou", "price": 24.75}]}');

INSERT INTO locations (type, name, latitude, longitude, attr) VALUES
('A', 'Cloud Gate', 41.8826572, -87.6233039, '{"category": "Landmark", "lastVisitDate":
"11/10/2019"}');

16. Reading Fields
SELECT name, latitude, longitude,
JSON_VALUE(attr, '$.details.foodType') AS food_type
FROM locations
WHERE type = 'R';
name latitude longitude food_type
Lou Malnati’s 42.0021628 -87.7255662 Pizza

17. Null Fields
SELECT type, name, latitude, longitude,
JSON_VALUE(attr,'$.details.foodType') AS food_type
FROM locations;
typename latitude longitude food_type
R Lou Malnati’s 42.0021628-87.7255662Pizza
A Willis Tower 41.8788764-87.6359149NULL

18. Contains Path
SELECT name, latitude, longitude,
JSON_VALUE(attr, '$.details.menu') AS menu
FROM locations
WHERE type = 'R' AND
JSON_CONTAINS_PATH (attr, 'one', '$.details.menu') = 1;
name latitude longitude menu
Lou Malnati’s 42.0021628-87.7255662
https://www.loumalnatis.com...

19. Contains Value
SELECT name, latitude, longitude
FROM locations
WHERE type = 'A' AND
JSON_CONTAINS(attr, '\"Landmark\"', '$.category') = 1;
name latitude longitude
Willis Tower 41.8788764 -87.6359149

20. Using JSON to filter
SELECT id, name, latitude, longitude
FROM locations
WHERE type = 'S' AND
JSON_VALUE(attr, '$.details.yearOpened') = 1924;
id name latitude longitude
7 Soldier Field 41.8623132 -87.6166884

21. LAYOUT
Title Only
PowerPoint Default
Reading objects
SELECT name, description,
JSON_QUERY(attr, '$.details') AS details
FROM locations
WHERE type = 'R'
name description details
The Publican
An award winning
American style
restaurant
{
"foodType": "American",
"menu": "thepublicanrestaurant.com/menu"
}

22. LAYOUT
Title Only
PowerPoint Default
SELECT name, description,
JSON_QUERY(attr, '$.teams') AS home_teams
FROM locations
WHERE type = 'S';
name description home_teams
United CenterAn indoor arena on the
Near West Side of Chicago
[
"Bulls",
"Blackhawks"
]
Reading Arrays

23. LAYOUT
Title Only
PowerPoint Default
Reading array elements
SELECT name, description,
JSON_VALUE(attr, '$.teams[0]') AS primary_home_team
FROM locations
WHERE type = 'S';
name description primary_home_team
United CenterAn indoor arena on the Near
West Side of Chicago
Bulls

24. Reading objects
SELECT name,
JSON_QUERY(attr, '$.teams') AS home_teams,
JSON_QUERY(attr, '$.details') AS details
FROM locations
WHERE type = 'S';
name home_teams details
United Center
[
"Bulls",
"Blackhawks"
]
{
"capacity": 23500,
"yearOpened": 1994
}

25. Indexing (1/2)
ALTER TABLE locations ADD COLUMN
food_type VARCHAR(25) AS (JSON_VALUE(attr, '$.details.foodType')) VIRTUAL;

EXPLAIN SELECT name, latitude, longitude
FROM locations
WHERE food_type = 'Mexican';
id select_type table type possible_keys
1 SIMPLE Locations ALL NULL

26. Indexing (2/2)
CREATE INDEX foodtypes ON locations(food_type);

EXPLAIN SELECT name, latitude, longitude
FROM locations
WHERE food_type = 'Mexican';
id select_type table ref possible_keys
1 SIMPLE Locations ref foodtypes

27. Modifying JSON
documents

28. Inserting fields
UPDATE locations
SET attr = JSON_INSERT(attr, '$.nickname',
'The Bean')
WHERE id = 8;
name nickname
Cloud Gate The Bean
SELECT name,
JSON_VALUE(attr, '$.nickname') AS
nickname
FROM locations
WHERE type = 'A';

29. Inserting array
UPDATE locations
SET attr =JSON_INSERT(attr, '$.foodTypes',
JSON_ARRAY('Asian', 'Mexican'))
WHERE id = 1;
name food_types
Saucy Porka
[
"Asian",
"Mexican"
]
SELECT name,
JSON_QUERY(attr, '$.foodTypes')
AS food_types
FROM locations
WHERE id = 1;

30. Adding array elements
UPDATE locations
SET attr = JSON_ARRAY_APPEND (attr,
'$.foodTypes', 'German’)
WHERE id = 1;
SELECT name, format, price,
JSON_QUERY(attr, '$.foodTypes')
AS food_types
FROM locations
WHERE id = 1;
name food_types
Saucy Porka
[
"Asian",
"Mexican",
"German"
]

31. Removing array elements
UPDATE locations
SET attr = JSON_REMOVE (attr, '$.foodTypes[2]')
WHERE id = 1;
SELECT name,
JSON_QUERY(attr, '$.foodTypes')
AS food_types
FROM locations
WHERE id = 1;
name food_types
Saucy Porka
[
"Asian",
"Mexican"
]

32. Creating JSON documents
from relational data

33. Returning relational data as JSON
SELECT JSON_OBJECT('name', name, 'latitude', latitude, 'longitude', longitude) AS data
FROM locations
WHERE type = 'S';
data
{
"name": "Wrigley Field",
"latitude": 41.9484384,
"longitude": -87.6553327
}

34. Relational + JSON
SELECT JSON_OBJECT('name', name, 'latitude', latitude, 'longitude', longitude, 'capacity',
JSON_VALUE(attr, '$.details.capacity')) AS data
FROM locations
WHERE type = 'S';
data
{
"name": "Wrigley Field",
"latitude": 41.9484384,
"longitude": -87.6553327
“capacity": 41649
}

35. Merging relational and JSON
SELECT JSON_MERGE(
JSON_OBJECT(
'name', name,
'latitude', latitude,
'longitude', longitude),
attr) AS data
FROM locations
WHERE type = 'R';
data
{
"name": "The Publican",
"latitude": ,
"longitude": ,
"details": {
"foodType": "American",
"menu": "http://www.thepublicanrestaurant.com/menu"},
"favorites": [
{"description": "Ribs with fries", "price": 16.99},
{"description": "BBQ Burger", "price": 12.99}
]
}

36. Enforcing data integrity
with JSON documents

37. JSON Constraints
ALTER TABLE locations ADD CONSTRAINT check_attr
CHECK (
type != 'S' or (type = 'S' and
JSON_TYPE(JSON_QUERY(attr, '$.details')) = 'OBJECT' and
JSON_TYPE(JSON_QUERY(attr, '$.details.events')) = 'ARRAY' and
JSON_TYPE(JSON_VALUE(attr, '$.details.yearOpened')) = 'INTEGER' and
JSON_TYPE(JSON_VALUE(attr, '$.details.capacity')) = 'INTEGER' and
JSON_EXISTS(attr, '$.details.yearOpened') = 1 and
JSON_EXISTS(attr, '$.details.capacity') = 1 and
JSON_LENGTH(JSON_QUERY(attr, '$.details.events')) > 0));

38. JSON Constraints
INSERT INTO locations (type, name, latitude, longitude, attr) VALUES
('S', 'Wrigley Field', 41.9484384, -87.6553327, '{"details": {"yearOpened": 1914, "capacity":
41649}');
ERROR 4025 (23000): CONSTRAINT `check_attr` failed for `test`.`locations`
no teams field!

39. JSON Constraints
INSERT INTO locations (type, name, latitude, longitude, attr) VALUES
('S', 'Wrigley Field', 41.9484384, -87.6553327, '{"details": {"yearOpened": "Last Year",
"capacity": 41649}, "teams": ["Cubs"]');
ERROR 4038 (HY000): Syntax error in JSON text in argument 1 to function 'json_type' at
position 1
"Last Year" is not a number!

40. Trying it out

41. Application Architecture
Front Middle
MariaDB
Back

42. Try it out!

https://github.com/mariadb-corporation/Developer-Examples

43. Webinar attendees only

1.Find two JSON functions that were not mentioned in this
presentation.
2.Email [email protected] and include:
a.Function name
b.Description of what the function is used for
3.Get swag!
Developer
Challenge!

44. THANK YOU!