Question 20844 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql find phrase in Json array

I am looking for a way of find rows by given element of the json table that match the pattern.

Lets start with mysql table:

CREATE TABLE `person` (
  `attributes` json DEFAULT NULL
);

INSERT INTO `person` (`attributes`)
VALUES ('[{"scores": 1, "name": "John"},{"scores": 1, "name": "Adam"}]');

INSERT INTO `person` (`attributes`)
VALUES ('[{"scores": 1, "name": "Johny"}]');

INSERT INTO `person` (`attributes`)
VALUES ('[{"scores": 1, "name": "Peter"}]');

How to find all records where attributes[*].name consists John* pattern?

In the John* case the query should return 2 records (with John and Johny).

Tags:

2

Answers


  1. 0 
    SELECT DISTINCT person.*
FROM person
CROSS JOIN JSON_TABLE(person.attributes, '$[*]' COLUMNS (name TEXT PATH '$.name')) parsed
WHERE parsed.name LIKE 'John%';

    https://sqlize.online/sql/mysql80/c9e4a3ffa159c4be8c761d696e06d946/

    Login or Signup to reply.
  2. 0 
    SELECT *
FROM `person`
WHERE JSON_EXTRACT(`attributes`, '$[*].name') LIKE '%John%';

    https://sqlize.online/s/P4

    or

    SELECT *
FROM `person`
WHERE `attributes` LIKE '%{"name": "John%';

    https://sqlize.online/s/b4

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search