How do I search for JSON object property values in PHP/Mysql?

Question

I need to filter the tags, they’re separated by comma:

{
"tags":"Tag 1, Tag 2, Tag 3"
}

Using

$busca = 'Tag 1';

  $sele2 = $pdo->prepare("SELECT * FROM quiz_publico WHERE quiz_json like :json_busca");
  $sele2->bindValue(':json_busca', '%' . $busca . '%', PDO::PARAM_STR);
  $sele2->execute();

It works, but results from other JSON positions are appearing. For example: has the field title, if you have the floor Tag 1 in him and in the field tags is not, it still returns that it found. And the page is intended only for tags and not general results.

It is possible to limit the results to tags only?

Structure of the database:

CREATE TABLE `quiz_publico` (
  `id` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `data` datetime NOT NULL DEFAULT current_timestamp(),
  `user_id` int(11) NOT NULL,
  `quiz_json` text COLLATE utf8_unicode_ci NOT NULL,
  `tipo` int(11) NOT NULL,
  `url` text COLLATE utf8_unicode_ci DEFAULT NULL,
  `publicado` int(11) NOT NULL DEFAULT 0,
  `aprovado` int(11) NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

I understand from your comment that your search is returning when JSON has Tag 1. Independent of object property. Example:

{"title":"Tag 1", "tags":"Tag 2"}

The problem is that Mysql understands this as a simple string, instead of a JSON ^{(Data Type)}. But in fact, the column to be filtered on your table is the type text:

`quiz_json` text COLL[...]

It should also be considered that JSON functions were added to Mysql in version 5.7:

"- the JSON functions Were Added in Mysql 5.7.8" - of documentation.

This means that Mysql itself, as of version 5.7.8, can filter the results based directly on the desired property. In your case, the property tags.

Example:

SELECT * FROM `quiz_publico` WHERE JSON_EXTRACT(`quiz_json`, "$.tags") LIKE '%Tag 1%';

In practice:

Schema (Mysql v5.7)

CREATE TABLE `quiz_publico` (
  `id` int(11) NOT NULL,
  `algo` varchar(50) DEFAULT NULL,
  `quiz_json` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_unicode_ci;

INSERT INTO `quiz_publico` VALUES (1, 'Tag 1', '{"tags":"Tag 1, Tag 2, Tag 3"}');
INSERT INTO `quiz_publico` VALUES (2, 'Tag 2', '{"tags":"Tag 2, Tag 3"}');
INSERT INTO `quiz_publico` VALUES (3, 'Tag 1', '{"tags":"Tag 1, Tag 2, Tag 3"}');
INSERT INTO `quiz_publico` VALUES (4, 'Foo',   '{"tags":"Tag 2, Tag 3"}');
INSERT INTO `quiz_publico` VALUES (5, 'Bar',   '{"tags":"Tag 3"}');
INSERT INTO `quiz_publico` VALUES (6, 'Fubá',  '{"tags":"Tag 1"}');
INSERT INTO `quiz_publico` VALUES (7, 'Baz',   '{"tags":"Tag 2, Tag 3"}');
INSERT INTO `quiz_publico` VALUES (8, 'Tag 3',  '{"tags":"Tag 1, Tag 3"}');
INSERT INTO `quiz_publico` VALUES (9, 'Tag 1',  '{"tags":"Tag 1, Tag 2"}');
INSERT INTO `quiz_publico` VALUES (10, 'Tag 4',  '{"tags":"Tag 2, Tag 3"}');
INSERT INTO `quiz_publico` VALUES (11, 'Foobá',  '{"title":"LipESprY", "tags":"Tag 2"}');
INSERT INTO `quiz_publico` VALUES (12, 'Foo',  '{"title":"Tag 1", "tags":"Tag 2"}');
INSERT INTO `quiz_publico` VALUES (13, 'Foobaz',  '{"title":"Tag 1", "tags":"Tag 1"}');
INSERT INTO `quiz_publico` VALUES (14, 'Tag 3',  '{"tags":"Tag 1"}');

Query #1

> SELECT * FROM `quiz_publico` WHERE JSON_EXTRACT(`quiz_json`, "$.tags") LIKE '%Tag 1%';

| id  | algo   | quiz_json                         |
| --- | ------ | --------------------------------- |
| 1   | Tag 1  | {"tags":"Tag 1, Tag 2, Tag 3"}    |
| 3   | Tag 1  | {"tags":"Tag 1, Tag 2, Tag 3"}    |
| 6   | Fubá   | {"tags":"Tag 1"}                  |
| 8   | Tag 3  | {"tags":"Tag 1, Tag 3"}           |
| 9   | Tag 1  | {"tags":"Tag 1, Tag 2"}           |
| 13  | Foobaz | {"title":"Tag 1", "tags":"Tag 1"} |
| 14  | Tag 3  | {"tags":"Tag 1"}                  |

See working on DB Fiddle

I need to point out that the JSON functions, or at least the JSON_EXTRACT, are not directly linked to columns of type json. But recommend that JSON-only columns are of the type json, since it is automatically validated by Mysql:

mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1, 2,'.

Of documentation.

---

Filtering records in PHP

If your database is older than version 5.7.8, I think the only alternative is to filter the results in PHP.

Take my example:

<?php
$stmt = $pdo->prepare('SELECT * FROM `quiz_publico` WHERE `quiz_json` LIKE :valor');
$stmt->bindValue(':valor', '%Tag 1%');
$stmt->execute();

$resp = $stmt->fetchAll(\PDO::FETCH_ASSOC);

$resp = array_filter($resp, function($arr){
    $json = json_decode($arr['quiz_json'], true);
    return (
        strpos($json['tags'], 'Tag 1') === false
        ? false
        : true
    );
});

print_r($resp);

See the result:

Array
(
    [0] => Array
        (
            [id] => 1
            [algo] => Tag 1
            [quiz_json] => {"tags":"Tag 1, Tag 2, Tag 3"}
        )

    [1] => Array
        (
            [id] => 3
            [algo] => Tag 1
            [quiz_json] => {"tags":"Tag 1, Tag 2, Tag 3"}
        )

    [2] => Array
        (
            [id] => 6
            [algo] => Fubá
            [quiz_json] => {"tags":"Tag 1"}
        )

    [3] => Array
        (
            [id] => 8
            [algo] => Tag 3
            [quiz_json] => {"tags":"Tag 1, Tag 3"}
        )

    [4] => Array
        (
            [id] => 9
            [algo] => Tag 1
            [quiz_json] => {"tags":"Tag 1, Tag 2"}
        )

    [6] => Array
        (
            [id] => 13
            [algo] => Foobaz
            [quiz_json] => {"title":"Tag 1", "tags":"Tag 1"}
        )

    [7] => Array
        (
            [id] => 14
            [algo] => Tag 3
            [quiz_json] => {"tags":"Tag 1"}
        )

)

Note that the ID record 12, that has the value Tag 1 on the property title was removed from the array in filtration (array_filter()).

---

Completion:

Despite the possibility of filtering in PHP, I recommend that if your Mysql is version earlier than 5.7.8, use for a version >= to this and already Filtre directly in the query.

Filtering in PHP would generate unnecessary information transport, which will be discarded without any use. For few records this may be imperceptible, but for thousands, or perhaps millions, this will generate a superfluous consumption of resources.

Recommended readings:

• Mysql 5.7 Reference Manual - 12.17 JSON Functions;
• Mysql 5.7 Reference Manual - 12.17 JSON Data Type;
• PHP: array_filter;
• PHP: JSON;