One of my students asked how you could get JSON data out in tabular format. I said they should look at Øystein Grøvlen’s JSON_TABLE – Best of Both Worlds blog post from 2018. Unfortunately, the student wanted another example with the Video Store model that we use in class.

For clarity, all path definitions start with a $ followed by other selectors:

• A period followed by a name, such as $.website
• [N] where N is the position in a zero-indexed array
• The .[*] wildcard evaluates all members of an object
• The [*] wildcard evaluates all members of an array
• The prefix and suffix wildcard, **, evaluates to all paths that begin with the named prefix and end with the named suffix

So, here’s a quick supplement to what’s already there. It assumes you created an …

Yesterday, I wrote a blog post that showed you how to write a query returning a JSON structure for a 1:many relationship. The relationship was between the member and contact table. It returns one account_number from the member table and a list of first_name and last_name columns from the contact table in a JSON structure.

One of my students asked why I choose to strip the backslashes with Python, and my reply was the SQL was already complex for most blog readers. The student asked but how would you do it in SQL. OK, that’s a fair question for two reasons. First, you don’t need to do in your local programs because it’ll run faster on the server. Second, if you strip the backslashes you can insert it into a standard JSON column. This blog post will show you how to do both.

You would use three REGEXP_REPLACE function calls, like:

Are they really tricks or simply basic techniques combined to create a solution. Before writing these mechanics for using native MySQL to create a compound JSON object, let me point out that the easiest way to get one is to use the MySQL Node.js library, as shown recently in my “Is SQL Programming” blog post.

Moving data from a relational model output to a JSON structure isn’t as simple as a delimited list of columns in a SQL query. Let’s look at it in stages based on the MySQL Server 12.18.2 Functions that create JSON values.

Here’s how you return single row as a JSON object, which is quite straightforward:

SELECT JSON_OBJECT('first_name',c.first_name,'last_name',c.last_name) AS json_result
FROM   contact c
WHERE …

I recently wanted to check all tables’ current AUTO_INCREMENT value (if any) for a database dump structure. I wasn’t sure if a tables’ Data Definition Language (DDL) statement contained the current AUTO_INCREMENT value or not. So after I loaded the dump file, I learned of a query to use and find out…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Image by  …

While I blogged about how to setup Node.js and MySQL almost two years ago, it was interesting when a student ran into a problem. The student said they’d configured the environment but were unable to use Node.js to access MySQL.

The error is caused by this import statement:

const mysql = require('mysql') 

The student got the following error, which simply says that they hadn’t installed the Node.js package for MySQL driver.

internal/modules/cjs/loader.js:638
    throw err;
    ^

Error: Cannot find module 'mysql'
    at Function.Module._resolveFilename (internal/modules/cjs/loader.js:636:15)
    at Function.Module._load (internal/modules/cjs/loader.js:562:25)
    at Module.require (internal/modules/cjs/loader.js:692:17)
    at require (internal/modules/cjs/helpers.js:25:18)
    at Object. (/home/student/Data/cit325/oracle-s/lib/Oracle12cPLSQLCode/Introduction/query.js:4:15)
    at Module._compile …

The MySQL DATE() function is a built-in function you use to extract just the date portion from a DATE or DATETIME value. Learn how to use the DATE() function in this blog post…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Image by MaeM from Pixabay

MySQL DATE() Function Syntax

The …

MySQL Partitioned Tables

Learning Outcomes

• Learn about List Partitioning.
• Learn about Range Partitioning.
• Learn about Columns Partitioning.
• Learn about Hash Partitioning.
• Learn about Key Partitioning.
• Learn about Subpartitioning.

Lesson Material

MySQL supports partitioning of tables. It supports range, list, hash, and key partitioning. Range partitioning lets you partition based on column values that fall within given ranges. List partitioning lets you partition based on columns matching one of a set of discrete values. Hash partitioning lets you partition based on the return value from a user-defined expression (the result from a stored SQL/PSM function). Key partitioning performs like hash partitioning, but it lets a user select one or more columns from the …

This post covers filtering in the WHERE clause conditional using the MySQL BETWEEN operator. Continue reading this excerpt of premium MySQL content I’m creating for anyone to learn how to use MySQL…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Suggested Reading

Visit any of these MySQL beginner-friendly articles for additional free learning resources:

• SELECT clause queries – MySQL Beginner Basics Series.
• …

Watch this quick in-depth video short and learn how to find all the calendar days for a specific month using built-in MySQL date functions and recursive Common Table Expressions (CTEs). This is a repost of a free article over on my Kofi page as part of the “MySQL Learning Tier” membership…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Image by  …

Oftentimes, you need multiple filter conditionals in a WHERE clause in order to target specific rows of data. Continue reading this blog post and learn how to use the AND logical operator in WHERE clause queries…

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

Practice Data

For the practice queries in this post, I’m using the ‘category’ table from the well-known Sakila database:

SELECT *
FROM category;

…