• Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar
  • Skip to footer

Coding Still

  • Home
  • About

Query JSON data as relational in MySQL

November 17, 2018 By _tasos Leave a Comment

MySQL can support the JSON datatype, and allows to write queries that can query the JSON data directly. In the case where the JSON value is an array of objects, we will see how we can query the table and retrieve the JSON data as relational data. We will see how we can use JSON_TABLE function to achieve this.
Fun fact: JSON_TABLE is MySQL’s first table function; the return value is not a scalar value, but a result set.

So, let’s consider the following table:

CREATE TABLE `json_data` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `json_column` JSON NULL,
  PRIMARY KEY (`id`));

We will insert a row with a JSON array

INSERT INTO `json_data` (`json_column`) 
VALUES ('[{\"firstName\":\"Mike\",\"lastName\":\"Wazowski\"},{\"firstName\":\"James\",\"lastName\":\"Salivan\"},{\"firstName\":\"Henry\",\"lastName\":\"Waternoose\"}]');

Note: Here is a better view of the JSON we will use in our examples

[
    { "firstName": "Mike", "lastName": "Wazowski" }, 
    { "firstName": "James", "lastName": "Salivan" }, 
    { "firstName": "Henry", "lastName": "Waternoose" }
]

Using the JSON_TABLE function we can return the data within the JSON array in a relational form.

SELECT jd.*
FROM json_data,
   JSON_TABLE(
     json_column,
     "$[*]"
     COLUMNS(
       name JSON PATH "$.firstName",
       surname JSON PATH "$.lastName"
     )
   ) AS jd

The query above will return the following dataset,and you have the JSON fields as db columns.

|-------------|-------------|
|   name      |  surname    |
|-------------|-------------|
|  Mike       |  Wazowski   |
|  James      |  Salivan    |
|  Henry      |  Waternoose |
|-------------|-------------|

You can also add columns to the table and have in the SELECT clause columns both from the table and the JSON data.

CREATE TABLE `row_json_data` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `company_name` VARCHAR(45) NULL,
  `city` VARCHAR(45) NULL,
  `stuff` JSON NULL,
  PRIMARY KEY (`id`));

For example, in the table above, we add the following data.

INSERT INTO `row_json_data` (`company_name`, `city`, `stuff`) 
VALUES ('Monsters Inc', 'Monstropolis', '[{\"lastName\": \"Wazowski\", \"firstName\": \"Mike\"}, {\"lastName\": \"Salivan\", \"firstName\": \"James\"}, {\"lastName\": \"Waternoose\", \"firstName\": \"Henry\"}]');

We can use both columns from the table and the ones defined in the JSON_TABLE function.

SELECT company_name, city, name, surname
FROM row_json_data,
	 JSON_TABLE(
	   staff,
	   "$[*]"
	   COLUMNS(
	     name JSON PATH "$.firstName",
	     surname JSON PATH "$.lastName"
	   )
	 ) AS jd

For that query, the result data will be similar if the data from the JSON_TABLE where joined with the other relational data.

|---------------|---------------|---------------|---------------|
|  company_name |  city         |   name        |  surname      |
|---------------|---------------|---------------|---------------|
|  Monsters Inc |  Monstropolis |  Mike         |  Wazowski     |
|  Monsters Inc |  Monstropolis |  James        |  Salivan      |
|  Monsters Inc |  Monstropolis |  Henry        |  Waternoose   |
|---------------|---------------|---------------|---------------|

Note: The above query will work not only for JSON columns, but also with VARCHAR and TEXT columns.

Filed Under: Databases Tagged With: MySQL, SQL

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Categories

  • .NET Development
  • ASP.NET
  • Databases
  • Fun
  • IIS
  • JavaScript
  • Web Development

Tags

.NET Core Android ANTLR ASP.NET Ajax ASP.NET Core ASP.NET MVC ASP.NET Web Forms AWS Bouncy Castle Chartjs cli Client info detection Comic Continuous integration CSS Data backup Date handling Firebase Firefox addons Github HigLabo HTML5 Image manipulation jQuery JWT MySQL Nodejs Nuget OAuth Objectionjs OOP openssl Oracle ORM PHP Regular expressions SEO Social media SQL SQL Server UI/UX Url rewriting Videos Visual Studio Web design

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Secondary Sidebar

Archives

  • July 2020
  • March 2020
  • August 2019
  • December 2018
  • November 2018
  • February 2018
  • August 2016
  • June 2016
  • May 2016
  • February 2016
  • January 2016
  • August 2015
  • July 2015
  • October 2014
  • July 2014
  • November 2013
  • April 2013
  • February 2013
  • January 2013
  • December 2012
  • November 2012
  • August 2012
  • May 2012
  • February 2012
  • December 2011
  • October 2011
  • September 2011
  • August 2011
  • July 2011
  • May 2011
  • April 2011
  • March 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • October 2010
  • September 2010
  • August 2010
  • July 2010

Footer

Recent Posts

  • Anatomy of an Objection.js model
  • Check your RSA private and public keys
  • Round functions on the Nth digit
  • Send FCM Notifications in C#
  • Jwt Manager
  • Things around the web #5
  • Query JSON data as relational in MySQL
  • Create and sign JWT token with RS256 using the private key
  • Drop all database objects in Oracle
  • Create and deploy a Nuget package

Latest tweets

  • Geekiness Intensifies.. NASA used Three.js to render a real-time simulation of this week's NASA rover landing on M… https://t.co/orgkXnYj9O February 19, 2021 18:12
  • Things I Wished More Developers Knew About Databases https://t.co/h4gfq6NJgo #softwaredevelopment #databases May 3, 2020 12:52
  • How a Few Lines of Code Broke Lots of Packages https://t.co/p7ZSiLY5ca #javascript May 3, 2020 12:48
  • Can someone steal my IP address and use it as their own? https://t.co/HoQ7Z3BG69 January 24, 2020 13:27
  • Organizational complexity is the best predictor of bugs in a software module https://t.co/aUYn9hD4oa #softwaredevelopment January 13, 2020 08:24
  • http://twitter.com/codingstill

Misc Links

  • George Liatsos Blog
  • Plethora Themes
  • C# / VB Converter
  • Higlabo: .NET library for mail, DropBox, Twitter & more

Connect with me

  • GitHub
  • LinkedIn
  • RSS
  • Twitter
  • Stack Overflow

Copyright © 2021 · eleven40 Pro on Genesis Framework · WordPress · Log in