Using JSON_CONTAINS in MySQL query gets no result

I want a query to return all the fields for all the entries in the table minto_regsix_session that include a value of 2 in the 'day' field. Following is a screenshot from phpMyAdmin of the table including the day field which contains a JSON array.

Screenshot of mysql table with JSON array

This query did NOT work.

SELECT * 
FROM `minto_regsix_session` 
WHERE JSON_CONTAINS(`day`, '2') = 1

The correct query requires including the double quotes from the array within the query.

SELECT * 
FROM `minto_regsix_session` 
WHERE JSON_CONTAINS(`day`, '"2"');

Originally posted and answered on Stack Exchange.

https://stackoverflow.com/questions/61868619/using-json-contains-in-mysql-query-gets-not-result

Here's an example SELECT statement to extract JSON data.

SELECT `id`, JSON_EXTRACT(`params`, "$.start_date"), JSON_EXTRACT(`params`, "$.end_date"), JSON_EXTRACT(`params`, "$.early_bird_date"), JSON_EXTRACT(`params`, "$.end_date"), JSON_EXTRACT(`params`, "$.prorate_date"), JSON_EXTRACT(`params`, "$.allow_prorate") FROM `minto_categories` WHERE `id` = 110

JSON in MySQL

Using json_decode in PHP is handing for extracting json parameters from PHP. Similarly, you can use JSON_EXTRACT in an SQL query.
 
SELECT JSON_EXTRACT(`params`, '$.parent_info_session') FROM `minto_categories` WHERE `id` = 96
 
Thise selects the data from the parent_info_session parameter from the JSON data that is stored in the params field of the minto_categories table.

Left Join

Using MYSQL JOIN always makes me go running back to the documentation. Hopefully this is enough of a reminder of the syntax.

SELECT `session_type` FROM `#__reg4_registrations` LEFT JOIN `#__reg4_sessions` ON #__reg4_sessions.id = #__reg4_registrations.sessionid WHERE #__reg4_registrations.SCNO = '{$SCNO}'

Can't change table structure due to date with 0000-00-00 default

If this happens, get the SQL commands to alter the tables and precede those commands with the following which temporarily disables strict mode an allows the changes to go through.

SET sql_mode = '';

MySQL JOIN

There have been many times when I knew that using a JOIN in my SQL query would have been the right way to get the data that I wanted from multiple tables but I never got them to work properly. I figured I better make notes on this now that it actually worked for me.

Here's an example of an SQL querey that I used to get the email addresses for the users in the comprofiler table (Community builder). In this case, there is a one-to-one mapping but when I add a WHERE to it then it will be a subset.

SELECT email FROM `skat_users` INNER JOIN `skat_comprofiler` ON skat_users.id = skat_comprofiler.user_id
 
Note: if you were using this in a joomla php file you'd use
SELECT email FROM `#__users` INNER JOIN `#__comprofiler` ON #__users.id = #__comprofiler.user_id

Recent Random Insights

  • 23 May 2020
    If you're using Joomla Component Builder and your admin list view isn't showing any items, even though there are items in the database, edit your admin view and make sure in the Admin behaviour column is set to Show in All List Views for at least one field.
  • 18 May 2020
    Ok. When creating a custom field: Here's an example of the text that goes in the "The php for the getOptions method" box. // gets the name and IDs of the description articles $db = JFactory::getDBO(); $query = $db->getQuery(true); $query->select($db->quoteName...
  • 18 May 2020
    I want a query to return all the fields for all the entries in the table minto_regsix_session that include a value of 2 in the 'day' field. Following is a screenshot from phpMyAdmin of the table including the day field which contains a JSON array. This query did NOT work. SELECT...
  • 07 February 2020
    Warning: session_start(): Failed to read session data: user (path: c:/wamp/tmp) in libraries\joomla\session\handler\native.php on line 260Error: Failed to start application: Failed to start the session Solution: check to make sure $dbprefix in the configuration.php file is set to correctly ma...
  • 12 September 2019
    If you get this message after switching to php version 7.x, change your code to from count() to count(get_object_vars()).
  • 08 August 2019
    Using json_decode in PHP is handing for extracting json parameters from PHP. Similarly, you can use JSON_EXTRACT in an SQL query.   SELECT JSON_EXTRACT(`params`, '$.parent_info_session') FROM `minto_categories` WHERE `id` = 96   Thise selects the data from the parent_info_session par...
  • 09 June 2019
    I you're getting Unable to Connect or ERR_CONNECTION_REFUSED when trying to connect to a site on localhost, check the configuration.php file to see if $force_ssl is set to 2. Change it to 0 and the problem should go away.