The JSON Playground in SingleStoreDB

SingleStoreDB’s new playground allows users to run queries over TPC-H, TPC-DS and a semi-structured game dataset sourced from a Confluent Cluster provided by DataGen.

In this post, you will learn how to work with JSON data in SingleStoreDB utilizing the json_game_data dataset in the playground, and get several tips and tricks on schema and query optimizations.

Explore the SingleStoreDB playground.

Navigate to the Playground

The SingleStoreDB playground allows for reads against the database, and opens with a SQL editor and four pre-loaded databases.

On the top left, click the database drop down and select the json_game_data database.

After the json_game_data is selected, clicking on the example queries button in the top right will open a list of queries that may be loaded directly onto the SQL editor.

These queries are designed to guide users in quickly learning some best practices on working with JSON in SingleStoreDB.

Tables With JSON Data in SingleStoreDB:

There are four Kafka pipelines ingesting JSON data into the workspace.

These pipelines are streaming into tables called:

player_activity
game_data
game_data
player_user_profile

SingleStoreDB natively supports the JSON datatype within the data definition language (DDL). To create a table with JSON data, simply add the JSON datatype column within the CREATE TABLE statement:

Table with a single JSON column

1CREATE TABLE `player_activity` (2 `json_player_activity` JSON COLLATE utf8_bin,3  KEY `__UNORDERED` () USING CLUSTERED COLUMNSTORE,4  SHARD KEY ()5 );

This is a valid way to bring in the data from the JSON pipeline and queries may be executed against the JSON column without problems, but the entire JSON will be scanned for every query.

Persisted computed columns

In SingleStoreDB, the recommended method of working with JSON is to have a column that stores JSON data, as well as PERSISTED COMPUTED columns on important keys that are frequently joined or filtered.

Creating these columns include setting the column name, the location of the key within the JSON and the persisted data type.

Here is an example of a persisted JSON column:

1`game_room_id` as json_player_activity::%game_room_id PERSISTED2BIGINT,

In this case the table definition is as follows:

1CREATE TABLE `player_user_profile` (2 `json_player_user_profile` JSON COLLATE utf8_bin,3 `gender` as json_player_user_profile :: $gender PERSISTED longtext4  CHARACTER SET utf8 COLLATE utf8_general_ci,5  `contact_info` as json_player_user_profile :: $contactinfo 6  PERSISTED longtext CHARACTER SET utf8 COLLATE utf8_general_ci,7 `city` as json_player_user_profile :: contactinfo :: $city 8  PERSISTED longtext CHARACTER SET utf8 COLLATE utf8_general_ci,9 `phone` as json_player_user_profile :: contactinfo ::% phone 10  PERSISTED bigint(20),11  `state` as json_player_user_profile :: contactinfo :: $state 12  PERSISTED longtext CHARACTER SET utf8 COLLATE utf8_general_ci,13 `zipcode` as json_player_user_profile :: contactinfo ::% zipcode 14  PERSISTED smallint(6),15  `interests` as json_player_user_profile :: $interests PERSISTED 16  longtext CHARACTER SET utf8 COLLATE utf8_general_ci,17  `interests_1` as json_player_user_profile :: interests :: `0` 18  PERSISTED longtext CHARACTER SET utf8 COLLATE utf8_general_ci,19   `interests_2` as json_player_user_profile :: interests :: `1` 20  PERSISTED longtext CHARACTER SET utf8 COLLATE utf8_general_ci,21   `interests_3` as json_player_user_profile :: interests :: `2` 22  PERSISTED longtext CHARACTER SET utf8 COLLATE utf8_general_ci,23   `regionid` as json_player_user_profile :: $regionid PERSISTED 24  longtext CHARACTER SET utf8 COLLATE utf8_general_ci,25   `registertime` as json_player_user_profile ::% registertime 26  PERSISTED bigint(20),27   `userid` as json_player_user_profile :: $userid PERSISTED longtext 28  CHARACTER SET utf8 COLLATE utf8_general_ci,29   KEY `registertime` (`registertime`) USING CLUSTERED COLUMNSTORE,30   KEY `userid` (`userid`) USING HASH,31   KEY `regionid` (`regionid`) USING HASH,32   KEY `interests` (`interests`) USING HASH,33   KEY `interests_1` (`interests_1`) USING HASH,34   KEY `interests_2` (`interests_2`) USING HASH,35   KEY `interests_3` (`interests_3`) USING HASH,36   SHARD KEY ()37   )38   ;

All other tables were created using similar syntax.

The column with all JSON data

The first column json_player_user_profile is where data is ingested into the Kafka pipeline, and the other columns are parsing the data within separate columns that are used for the shard, sort and hash keys.

The data shape for each rows is as follows:

1{2 "contactinfo": {3    "city": "San Carlos",4    "phone": "6502215368",5    "state": "CA",    6    "zipcode": "94070"7  },8   "gender": "OTHER",9   "interests": [10   "Game",11   "Sport" 12  ],   13   "regionid": "Region_4",14   "registertime": 1487715806379,15   "userid": "User_1"16  }

All other columns are computed from the json_player_user_profile column.

Grabbing the desired key within the whole JSON column can be done by selecting the json_player_user_profile column, and adding :: to go to the next level within the JSON.

For example, to get the value for the key “userid” which is on the first level of the JSON, the syntax would be as follows:

1json_player_user_profile::$userid

Nested and subsequent JSON levels may be accessed using additional :: syntax after each level

For example to get the nested key of “city” within “contact_info”, the syntax looks like this:

1json_player_user_profile::$interests

`%` and `$` symbols

When parsing out the computed columns, the semi-colon notation ::, ::$, and ::% may be used to update JSON objects:

::$ will declare the object as a string
::% will declare the object as a double

Querying the JSON Tables

As soon as the data streams into SingleStoreDB, it is queryable.Here are the first 10 rows of the player_user_profile table:

Q1. Insert a JSON row, then find it by city

New JSON rows can be added by inserting into the whole JSON column. In this case the column is json_player_user_profile:

1-- insert row2insert into player_user_profile (3 json_player_user_profile4 )5values6 (7   '{"contactinfo":{"city": "Guilford",8     "phone": "9876543210",9     "state": "CT",10     "zipcode": "06437"} ,11   "gender": "MALE",12   "interests": [13     "SQL",14     "SingleStore"15   ],16   "regionid": "Region_1",17   "registertime": 1493582430152,18   "userid": "User_10"} '19 );

Let’s take a look at the newly inserted row by checking the city name:

1SELECT * FROM player_user_profile2WHERE json_player_user_profile::contactinfo::$city = 'Guilford';

Q2. Update the inserted row and return it using the phone persisted computed column, or the JSON_LEGNTH function

By updating the JSON key within the json_player_user_profile column, persisted computed columns will automatically be updated with the new value.

Here’s an example of adding a new interest “JSON” to our newly created row:

1UPDATE player_user_profile2SET json_player_user_profile::interests = 3'["SQL","SingleStore","JSON"]'4WHERE phone = 9876543210;5 6SELECT * FROM player_user_profile WHERE phone = 9876543210;7 8-- This is the only user with 3 interests9SELECT * FROM player_user_profile WHERE JSON_LENGTH(interests) = 3;

Q3. Selecting an index of a JSON array

Notice the JSON rows in the player_user_profile have a nested JSON array ‘interests’:

1SELECT json_player_user_profile::$interests2FROM player_user_profile3LIMIT 10;

Select the index within the array using this syntax:

1-- Return the first index of the interests array2SELECT json_player_user_profile::interests::`0`3FROM player_user_profile4LIMIT 10;

The ::0 syntax after interests is selecting the first index within the interests array. Replace the ‘0’ with ‘1’ to select the second position within the index, and so on.

Q4a. Compare performance of querying the whole JSON column vs. persisted computed columns (whole JSON)

The keys have already been parsed into computed columns for the player_user_profile table. These columns eliminate the need to scan the entire JSON for queries that call frequently used keys within the JSON.

For the comparison, the third interest is selected from the new row that has been added with the filter on the phone number ‘9876543210’.

First, the query is run using the whole JSON column ‘json_player_user_profile’:

1SELECT json_player_user_profile::interests::`2` interests_3,2 json_player_user_profile::contactinfo::%phone phone3FROM player_user_profile4WHERE json_player_user_profile::contactinfo::%phone = 9876543210;

Using the Visual Profiler, the operations to execute the query (along execution times) are visualized:

On the summary located in the top right corner, the total execution time is 447ms.

Q4b. Compare performance of querying the whole JSON column vs. persisted computed columns (persisted computed)

The same query as Q4a is run using the persisted computed columns, instead of scanning through the entire JSON column.

1SELECT interests_3,2 phone3FROM player_user_profile4WHERE phone = 9876543210;

Using the Visual Profiler, you’ll notice the execution time is only 12ms — a 37.25x improvement for this query!

Q5. Joining the player_data, player_activity, and game_data table using Common Table Expressions (CTE)

After exploring the data, it seems that the data within all three of these tables are randomly generated, but there are columns like player_id and game_id that may be joined together.

Let’s create a dataset by joining these tables to use for the next few queries:

1WITH p_data AS (SELECT2 player_id player_id2,3 player_name4 FROM player_data5 GROUP BY player_id26 ORDER BY player_id27 ),8p_activity AS (SELECT9 coordinates,10 game_room_id,11 player_id,12 points13 FROM player_activity14 GROUP BY game_room_id, player_id15 ORDER BY game_room_id16 ),17g_data AS (SELECT18 created_date,19 game_id,20 room_name21 FROM game_data22 GROUP BY game_id23 ORDER BY game_id24 )25SELECT26 player_id,27 player_name,28 coordinates,29 game_room_id,30 points,31 created_date,32 room_name33FROM (SELECT34     coordinates,35     created_date,36     game_room_id,37     room_name,38     player_id,39     points40     FROM p_activity pa41     LEFT JOIN g_data gd42     ON pa.game_room_id = gd.game_id43     ORDER BY game_room_id44   ) AS pgd45LEFT JOIN p_data pd46ON pgd.player_id = pd.player_id247ORDER BY game_room_id48;

Q6. Show joined tables as a view

The result in Q5 will be used for future queries, so a view of this table called ‘joined_tables’ has been added.

1/*2CREATE VIEW joined_tables AS3 WITH p_data AS (SELECT4 player_id player_id2,5 player_name6 FROM player_data7 GROUP BY player_id28 ORDER BY player_id29 ),10 p_activity AS (SELECT11   coordinates,12   game_room_id,13   player_id,14   points15   FROM player_activity16   GROUP BY game_room_id, player_id17   ORDER BY game_room_id18 ),19 g_data AS (SELECT20   created_date,21   game_id,22   room_name23   FROM game_data24   GROUP BY game_id25   ORDER BY game_id26 )27 SELECT28   player_id,29   player_name,30   coordinates,31   game_room_id,32   points,33   created_date,34   room_name35FROM (SELECT36       coordinates,37       created_date,38       game_room_id,39       room_name,40       player_id,41       points42       FROM p_activity pa43      LEFT JOIN g_data gd44       ON pa.game_room_id = gd.game_id45       ORDER BY game_room_id46     ) AS pgd47 LEFT JOIN p_data pd48 ON pgd.player_id = pd.player_id249 ORDER BY game_room_id50;51*/

See the view:

1SELECT * FROM joined_tables;

Q7. Output table rows as JSON

SingleStoreDB has several useful functions for working with JSON. To see the rows from Q6 as JSON, the TO_JSON function may be utilized:

1SELECT TO_JSON(joined_tables.*) AS joined_tables_as_json FROM 2joined_tables;

Q8. Build nested JSON objects from columns on your table

Using the joined_tables view, customized JSON objects may be created using the JSON_BUILD_OBJECT function.

The following example demonstrates creating a nested object that includes player data and game data as well as typecasting values using :>datatype syntax:

1SELECT JSON_BUILD_OBJECT(2'player_details',3 JSON_BUILD_OBJECT(4   'player_id', player_id:>INT,5   'player_name', player_name:>VARCHAR(100)6 ),7'game_details',8 JSON_BUILD_OBJECT(9   'game_room_id', game_room_id:>INT,10   'room_name', room_name:>VARCHAR(25)11 )12)13from joined_tables;

Q9. The top 10 players with the highest average points per game in the room “Arcade — Expert”

Using the joined_tables view, a dataset using three JSON tables is created where analytical queries may be run.

The number of games, total points and average points per game for each player is aggregated with query:

1SELECT player_id,2 player_name,3 room_name,4 games_played,5 total_points,6 avg_points_per_game,7 rank() over (order by avg_points_per_game desc) rank_in_arcade_expert8FROM (SELECT9 player_id,10 player_name,11 COUNT(game_room_id) games_played,12 SUM(points) total_points,13 AVG(points) avg_points_per_game,14 room_name15 FROM joined_tables16 WHERE room_name = 'Arcade -- Expert'17 GROUP BY player_id)18ORDER BY rank_in_arcade_expert19LIMIT 10;

Q10. The top three game modes for each player and percentage of games played in each room from a JSON view

For the final query, a view has been created of the joined_tables in a single JSON column:

1CREATE VIEW joined_tables_json AS2SELECT JSON_BUILD_OBJECT('player_id',player_id:>INT,3 'game_room_id', game_room_id:>INT,4 'room_name', room_name:>VARCHAR(25),5 'player_name', player_name:>VARCHAR(25),6 'points', points:>INT) AS joined_tables_json7FROM joined_tables;

The JSON is extracted from the joined_tables_json view, and joining the table with CTE’s allows for robust analytics on our dataset.

1WITH games_per_player AS (SELECT2 joined_tables_json::%player_id player_id,3 count(*) total_game_played_by_player4 FROM joined_tables_json5 GROUP BY joined_tables_json::$player_name6 ORDER BY player_id7),8game_data_by_player AS (SELECT9 joined_tables_json::%game_room_id game_room_id,10 joined_tables_json::%player_id player_id,11 joined_tables_json::$player_name player_name,12 COUNT(*) games_played,13 RANK() OVER (PARTITION BY joined_tables_json::%player_id  ORDER BY 14 COUNT(*) DESC) room_rank,15  joined_tables_json::$room_name room_name16  FROM joined_tables_json17  GROUP BY player_id, room_name18  ORDER BY player_id, room_rank19)20SELECT gp.player_id,21 player_name,22 games_played,23 room_name,24 total_game_played_by_player,25 ROUND(games_played/total_game_played_by_player *100,2) 26 percent_of_games_played,27 room_rank28FROM game_data_by_player gp29INNER JOIN games_per_player gpp30ON gp.player_id = gpp.player_id31where room_rank <=332ORDER BY player_id, percent_of_games_played desc;

In Summary

Working with JSON data in SingleStoreDB is easy and powerful. Using the right strategies outlined here including Persisted Computed Columns, SingleStoreDB JSON functions and schema design decisions may power JSON-intensive applications at scale!

See these queries in action in our new SingleStoreDB Playground.

Try SingleStoreDB free today.