Exploring the functionality of MySQL's LIKE operator on fields stored as JSON-encoded data

I've been struggling to retrieve a table row using the following query:

SELECT * FROM `table` WHERE `field` LIKE "%\u0435\u0442\u043e\u0442%"

The actual content of the field is: 

Field                                     
--------------------------------------------------------------------
\u0435\u0442\u043e\u0442 \u0442\u0435\u043a\u0441\u0442 \u043d\u0430

Despite my efforts, I can't seem to make it work correctly. I have tried various combinations with the backslash character:

LIKE "%\\u0435\\u0442\\u043e\\u0442%"
LIKE "%\\\\u0435\\\\u0442\\\\u043o\\\\u0442%"

Unfortunately, none of them are giving me the desired results.

If anyone could provide some guidance on what might be causing this issue, it would be greatly appreciated. Thank you in advance!

EDIT

Good news - problem solved! Resolution: even after fixing the query syntax, it wasn't returning any results. Changing the field to BINARY made the query function as intended.

mysqljson

Answer №1

Referencing the information found in String Comparison Functions:

Note

MySQL utilizes C escape syntax within strings, such as using “\n” to denote a newline character. It is important to note that any backslashes used in LIKE statements must be doubled. For instance, when searching for “\n”, it should be expressed as “\\n”. When searching for “\”, it should be specified as “\\\\”; this precaution is necessary because the backslashes are stripped first by the parser and then again during pattern matching, resulting in a single backslash ready to match against.

Consequently:

SELECT * FROM `table` WHERE `field` LIKE '%\\\\u0435\\\\u0442\\\\u043e\\\\u0442%'

View it live on sqlfiddle.

Answer №2

For individuals proficient in PHP, this code snippet has proven to be quite handy and effective.

$where[] = 'organizer_info LIKE(CONCAT("%", :organizer, "%"))';
$bind['organizer'] = str_replace('"', '', quotemeta(json_encode($orgNameString)));

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Minor login form mishap

I am currently working on creating a compact login form where users need to input their username and password. If the entered credentials match those stored in the database, they will be directed to the home page. Here's my progress so far: <html ...

phpmysql

submitting a JObject as a payload to a request

I just came across an interesting article by Rick Strahl on passing multiple POST parameters to Web API Controller methods. You can check it out here. In the article, he talks about using JObject in the action and provides an example of a controller imple ...

asp.net-mvcjsonjson.net

What are the situations when triggers demonstrate superior performance?

What are the advantages of using a trigger for performance optimization? Consider this scenario: after an insert operation, you also need to update a separate table with the total count of rows from the original table. Instead of executing the update quer ...

phpmysql

combining multiple ajax requests

Thank you for taking the time to review this. Currently, I have a MySQL database that populates a dropdown menu. Upon selection, it fills a second dropdown through an AJAX xmlhttprequest to a PHP file which executes a query on the database. I am now look ...

phpmysqlajax

How can I retrieve objects using the JSON function?

I need to design a function that returns objects like the following: function customFunction(){ new somewhere.api({ var fullAddress = ''; (process address using API) (return JSON data) })open(); } <input type= ...

javascriptjson

Guide to changing the size of an image within a table using PHP

Looking for assistance with resizing images retrieved from a MySQL database using PHP. <?php $con = mysql_connect('db', 'table', 'p/w'); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db ...

phpmysqlimageresize

Steps for displaying multiple values in a single <td> tag

I am looking to display my values based on id in a single <td> for each iteration, separated by comma(,). However, my current code generates multiple <td>'s depending on the number of iterations. For example, if I have 3 id's, then it ...

phpmysqlhtml-table

Looking to set up a web service that can handle incoming posts, but unsure about the best way to send a response back to jQuery

Good morning, I have a Go code that processes a JSON post request and performs certain actions. However, I am looking to send back either the result or a message to jQuery. package main import ( "fmt" "log" "net/http" "encoding/json" ...

javascriptjqueryjsonajaxgo

The JSONObject encountered an exception as it could not find a value for the

Encountering the same old exception while developing my android application The 'message' key is missing in the JSON data I'm certain that the JSONObject I possess holds the desired information: View and analyze my JSON Object for bette ...

androidjson

Would it be wise to store JSON files for my iPhone app on external servers?

Looking to enhance my app with "self-updating" features that refresh its content monthly. My current Squarespace website lacks file hosting capabilities, and I'm hesitant to invest in another domain just for a JSON file update. Are there any third-pa ...

iosjsonhosting

Tips for understanding nested JSON or array data structure?

My data is stored in a constant called translations and it looks like this: { "item-0": { "_quote-translation": "translation Data", "_quote-translation-language": "English", "_quote-trans ...

javascriptarraysjsonnext.jsgraphql

Guide on accessing a web service using a PHP POST request

Today, I encountered a challenge where I need to access a web service that contains JSON data. In order to do so, I was instructed to utilize the PHP POST method to log into the web service. The instructions included an array with 3 key-value pairs. { " ...

phpjsonweb-servicesrestpost

Trouble displaying JSON data in HTML using *ngFor in Angular

Just starting out with angular and I've created a services class that returns product details in JSON format. api.service.ts import { Injectable } from '@angular/core'; import { Http } from '@angular/http'; import 'rxjs/add/ ...

jsonangular

Utilizing Retrofit for parsing JSON data of this nature

Looking to utilize the retrofit library for parsing this particular data format. Json { slots” { “123”: [ { “an”: ”yy” “pa”:”ya” }, { “an”: ”s ...

androidjsonparsingretrofitretrofit2

Converting a large XML file to JSON using Node.js示例

I'm still new to Node.js and facing a challenge with converting 83 XML files, each approximately 400MB in size, into JSON format. Every file contains extensive data similar to the below structure: <case-file> <serial-number>75563140< ...

jsonnode.jsxml-parsing

Exploring Joins in Laravel 4 Query Builder

In my database, I have tables named "airport" and "route". The "id" of "airport" is a foreign key in "route" (i.e. Origin, Destination). Airport +-------+-------------+-----------------------+ | id | airportcode | Location | +-------+--- ...

phpmysqllaravel

PHP configuration file, method for establishing a connection with an error handling mechanism

I am currently working with a configuration file that is linked to a page, as mentioned in a previous resolved answer. As someone who is new to PHP, I have been modifying code from a tutorial on creating a blog. My task involves populating rows in an HTML ...

phpmysqlconfig

What is the reason for needing to execute the "FLUSH HOSTS;" command daily in my project?

My project using a LEMP stack is currently hosted on Digital Ocean and I'm encountering an error: Host '167.71.227.200' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' Every day, I have to use F ...

phpmysqlnginxdevopsdigital-ocean

Working with varying data types in JSON using Circe

When dealing with different data types in the same JSON field, such as: "need_exp":1500 or "need_exp":"-" How can this be handled without completely rewriting the decoder? Is there a way to instruct the decoder to automatically convert all integers to ...

javajsonscalacirce

What is the best way to fetch images from a JSON object in a React application?

I have been trying to load images from an array of objects in React, but I keep encountering a "module not found" error. This issue has been frustrating me for the past couple of days. Can someone please help me troubleshoot this problem or provide some su ...

javascripthtmljsonajaxreactjs