How to Search Json Array in MySQL

JSON_CONTAINS() with an array of JSON objects in MySQL

Yes, it is possible using the following syntax:

SELECT JSON_CONTAINS(arr, '{"name": "jay"}') from test WHERE id=1;

db<>fiddle demo

Example:

+-----+--------------------------------------------------------+---+
| id  |                          arr                           | r |
+-----+--------------------------------------------------------+---+
|  1  | [{"name": "aman"}, {"name": "jay"}]                    | 1 |
|  2  | [{"name": "yash"}, {"name": "aman"}, {"name": "jay"}]  | 1 |
|  3  | [{"name": "yash"}, {"name": "aman"}]                   | 0 |
+-----+--------------------------------------------------------+---+

read json array on mysql query

You can use JSON_TABLE for the same

SELECT get_ddi.* 
FROM my_table, 
     JSON_TABLE(my_column, '$[*]' COLUMNS (
                my_column VARCHAR(40)  PATH '$.ddi')
     ) get_ddi;

**Schema (MySQL v8.0)**

    DROP TABLE IF EXISTS `my_table`;
    
    CREATE TABLE IF NOT EXISTS `my_table` (
      `id` SERIAL,
      `my_column` JSON
    );
    
    INSERT INTO `my_table` (`id`, `my_column`)
    VALUES
      (1, '[
      {
        "ddi": "55",
        "routing_id": "7",
        "price": {
          "mt": 0.0285,
          "mo": 0.0285
        }
      },
      {
        "ddi": "598",
        "routing_id": "10",
        "price": {
          "mt": 0.06,
          "mo": 0.06
        }
      }
    ]');

**Query #1**

    SELECT get_ddi.* 
    FROM my_table, 
         JSON_TABLE(my_column, '$[*]' COLUMNS (
                    my_column VARCHAR(40)  PATH '$.ddi')
         ) get_ddi;

**Output**

| my_column |
| --------- |
| 55        |
| 598       |

As per @Guilherme Mascarenhas comments below, the solution needed was for MariaDb. As of version 10.2.31, JSON_TABLE function doesn't exist for MariaDB.
A hacky solution could be to use appropriate mariadb sequence table(Depending on the number of rows). JSON_UNQUOTE removes the quote from the extracted value. seq is used as index to get the specified ddi value from the array.

SELECT 
    JSON_UNQUOTE(JSON_EXTRACT(t.my_column, CONCAT('$[', seq_0_to_100.seq, '].ddi'))) AS getddi
FROM my_table t
JOIN seq_0_to_100
HAVING getddi IS NOT NULL;

**Output**

| my_column |
| --------- |
| 55        |
| 598       |

How can I search for value in json array in mysql

Using JSON_CONTAINS:

SELECT * FROM myTable WHERE JSON_CONTAINS(value, '"B"', '$');

db<>fiddle demo

MySQL search json value by key in array

You're essentially meaning to apply selection and projection to the array elements and object fields of your JSON document. You need to do something like a WHERE clause to select a "row" within the array, and then do something like picking one of the fields (not the one you used in your selection criteria).

These are done in SQL using the WHERE clause and the SELECT-list of columns, but doing the same with JSON isn't something you can do easily with functions like JSON_SEARCH() and JSON_CONTAINS().

The solution MySQL 8.0 provides is the JSON_TABLE() function to turn a JSON document into a virtual derived table — as though you had defined conventional rows and columns. It works if the JSON is in the format you describe, an array of objects.

Here's a demo I did by inserting your example data into a table:

create table mytable ( mycol json );

insert into mytable set mycol = '[{"Race": "Orc", "strength": 14}, {"Race": "Knight", "strength": 7}]';

SELECT j.* FROM mytable, JSON_TABLE(mycol, 
  '$[*]' COLUMNS (
    race VARCHAR(10) PATH '$.Race', 
    strength INT PATH '$.strength'
  )
) AS j;
+--------+----------+
| race   | strength |
+--------+----------+
| Orc    |       14 |
| Knight |        7 |
+--------+----------+

Now you can do things you normally do with SELECT queries, like selection and projection:

SELECT j.strength FROM mytable, JSON_TABLE(mycol, '$[*]' 
  COLUMNS (
    race VARCHAR(10) PATH '$.Race', 
    strength INT PATH '$.strength'
  )
) AS j 
WHERE j.race = 'Orc'
+----------+
| strength |
+----------+
|       14 |
+----------+

This has a couple of problems:

1. You need to do this every time you query the JSON data, or else create a VIEW to do it.

2. You said you don't know the attribute fields, but to write a JSON_TABLE() query, you must specify the attributes you want to search and project in your query. You can't use this for totally undefined data.

I've answered quite a number of similar questions about using JSON in MySQL. I've observed that when you want to do this sort of thing, treating a JSON document like a table so you can apply condition in the WHERE clause to fields within your JSON data, then all your queries get a lot more difficult. Then you start feeling like you would have been better off spending a few minutes to define your attributes so you could write simpler queries.

MySQL how to search JSON Array or JSON_CONTAINS in where statement with column name

SELECT
    `Categories`.`id`,
    `Categories`.`name`,
    (
    SELECT
        COUNT(`id`)
    FROM
        `Products`
    WHERE
        JSON_CONTAINS(
            `Products`.`category_id`,
            CONCAT('"',`Categories`.`id`,'"')
        )
) AS `products_count`
FROM
    `Categories`
ORDER BY `products_count`

fiddle

Values in JSON have string type whereas in products table they are numbers. MySQL won't convert datatypes for JSON implicitly rather than another datatypes, because " chars in JSON are not only datatype marks but, from MySQL looking point, they are a part of value. So add dquote chars to the value to be searched for.

How to select JSON object from JSON array field of mysql by some condition

So current best solution is mine:

SELECT 
    test.id,
    JSON_EXTRACT(test.json_list, CONCAT('$.list[', ind.ind, ']')),
    ind.ind
FROM
    test
CROSS JOIN
    (SELECT 0 AS ind UNION ALL SELECT 1 AS ind UNION ALL SELECT 2 AS ind) ind
WHERE
    JSON_LENGTH(json_list, "$.list") > ind.ind
    AND JSON_EXTRACT(json_list, CONCAT('$.list[', ind.ind, '].type')) = "color";

Search in mysql json column to check multiple criteria on same index of array

In MySQL 8.0, you can use handy JSON function json_table() to convert a json array to rows. You can then search the resultset.

The following query gives you all viewids whose at least one array element with attribute uId is equal to 'UR000001' and attribute view is 'Y':

select v.viewid
from vw_viewmaster v
where exists (
    select 1
    from json_table(
        v.security -> '$.security',
        '$[*]'
        columns(
            uid  varchar(50) path '$.uId',
            edit varchar(1)  path '$.edit',
            view varchar(1)  path '$.view'
        )
    ) x
    where x.uid = 'UR000001' and x.view = 'Y'
);

For your dataset, this produces:

| viewid       |
| ------------ |
| VW0000000002 |
| VW0000000014 |

---

If you want the details of the matching array object(s), then:

select v.viewid, x.*
from vw_viewmaster v
cross join json_table(
    v.security -> '$.security',
    '$[*]'
    columns(
        rowid for ordinality,
        uid   varchar(50) path '$.uId',
        edit  varchar(1)  path '$.edit',
        view  varchar(1)  path '$.view'
    )
) x
where x.uid = 'UR000001' and x.view = 'Y'

As a bonus, rowid gives you the index of the matching object in the JSON array (the first object has index 1).

This yields:

| viewid       | rowid | uid      | edit | view |
| ------------ | ----- | -------- | ---- | ---- |
| VW0000000002 | 1     | UR000001 | N    | Y    |
| VW0000000014 | 1     | UR000001 | N    | Y    |

However please note that if more than one object in the array that satisfies the conditions, the above query would generate more than one row per row in the original table (this is why I used exists in the first query).

---

Related Topics