Getting data from json array mysql

Asked Oct 07 '22 at 10:52

Active Oct 08 '22 at 07:07

Viewed 32 times

Below is my json data which is saved in cold_storage_data column of inventory table

[{
    "qty_in_storage": "10",
    "cold_storage_id": "2",
    "cold_storage_lot_number": "678678"
}, {
    "qty_in_storage": "15",
    "cold_storage_id": "1",
    "cold_storage_lot_number": "678679"
}]

Now I want to retrieve the data based on the cold_storage_lot_number. So I have tried below solutions but none of them are working for me

SELECT
    *
FROM
    test.inventories
WHERE
    JSON_EXTRACT( cold_storage_data, "$.cold_storage_lot_number" ) = "678679";

SELECT
    *
FROM
    test.inventories
WHERE
    json_contains( cold_storage_data, "678679", "$.cold_storage_lot_number" );

SELECT
    *
FROM
    test.inventories
WHERE
    cold_storage_data->"$.cold_storage_lot_number" = "678679";

Where I am going wrong?

The query provided by @dai dbfiddle working fine with mysql version 8 when I am testing it with MariaDB version 10.3.35 it is not working. MariaDB dbfiddle

edited Oct 08 '22 at 07:07

asked Oct 07 '22 at 10:52

urfusion

5,528
5
50
87

Is there a reason your data isn't normalized? – Dai Oct 07 '22 at 10:54
I have to keep in on table – urfusion Oct 07 '22 at 10:57
What version of MySQL are you using? – Dai Oct 07 '22 at 10:57
Mysql Version 8 – urfusion Oct 07 '22 at 10:57
To confirm, the `cold_storage_data` column in a single row contains a JSON array with multiple `object` values? Please post a DBFiddle/SQLFiddle with exemplary test data. – Dai Oct 07 '22 at 10:58
Ok, let me create a demo – urfusion Oct 07 '22 at 10:59
@Dai https://www.db-fiddle.com/f/vmkB7bZdTCV55ExL7aq68u/0 – urfusion Oct 07 '22 at 11:07
See the second query here: https://www.db-fiddle.com/f/vmkB7bZdTCV55ExL7aq68u/1 – Dai Oct 07 '22 at 11:16
Working, Thanks. Can you please tell why these basic function of `json` was not working. While it is a valid `json` – urfusion Oct 07 '22 at 11:25
Part of the problem is that your "root" JSON value is an array `[]`, not an `object`-literal. – Dai Oct 07 '22 at 11:26
Ok, So the data should be in `{ "data": [{ "qty_in_storage": "10", "cold_storage_id": "2", "cold_storage_lot_number": "678678" }, { "qty_in_storage": "15", "cold_storage_id": "1", "cold_storage_lot_number": "678679" }] }` format? – urfusion Oct 07 '22 at 11:31
1

...you should just normalize your table instead of storing JSON inline: it's a massive anti-pattern that will wreck performance. – Dai Oct 07 '22 at 11:36
The staging server have mariadb version 10.3 on which this query is not working. https://dbfiddle.uk/7tzTZfDj – urfusion Oct 08 '22 at 06:51

Getting data from json array mysql

0 Answers0