MySQL JSON Field Type Indexed Array

[Heretic86]

Hi!

I want to have an Indexed Array with Indeces I define.

In PHP I can do it like this:

$my_array = [1=> "foo", 3=> "bar", 11 => "baz"];

I've tried this but MySQL JSON field type does not like it...

[ 1 : {"name" : "Foo"}, 3 : {"name" : "bar"}]

The variations I have tried end up making the Index an Object Property not an Array Index.  How do I set the Index in a JSON Field Type in a MySQL database?

[Barand]

I couldn't get it to work with your numeric keys, but this worked

$pdo->exec("DROP TABLE IF EXISTS mytest");
$pdo->exec("CREATE TABLE mytest (
    id int not null auto_increment primary key,
    jdata JSON
)
");

$my_array = ["one" => "foo", "three" => "bar", "eleven" => "baz"];
$jdata = json_encode($my_array);

$stmt = $pdo->prepare("INSERT INTO mytest (jdata) VALUES (?)");
$stmt->execute([$jdata]);

// THEN

select id, jdata->>"$.three" from mytest;
+----+-------------------+
| id | jdata->>"$.three" |
+----+-------------------+
|  1 | bar               |
+----+-------------------+

Also, this worked

$my_array = ["foo", "bar", "baz"];
$jdata = json_encode($my_array);

$stmt = $pdo->prepare("INSERT INTO mytest (jdata) VALUES (?)");
$stmt->execute([$jdata]);

// THEN

select id, jdata->>"$[1]" from mytest;
+----+----------------+
| id | jdata->>"$[1]" |
+----+----------------+
|  1 | bar            |
+----+----------------+

 

[Heretic86]

It appears that MySql does not play well with numbered indexes.  So... moving on...

If one field had an array of objects, such as a list of songs or something, how would we extract all names from one result?

For example, the database result for just "pets" could be something like this string:

[{"id" : "1", "name": "fido", "age":"5"}, {"id" : "2", "name": "rover", "age":"3"},{"id" : "3", "name": "woofie", "age":"1"}]

Then from that list, get just the Name property from each member of the array?  Admittedly, this may not be the best approach, its just me learning more about what the potential of using JSON in MySQL offers.  Theres just gonna be a LOT of objects and I was hoping to keep things in one table cell that held a lot of data...

[Barand]

One way would be

$jdata = '[{"id" : "1", "name": "fido", "age":"5"}, {"id" : "2", "name": "rover", "age":"3"},{"id" : "3", "name": "woofie", "age":"1"}]';

$stmt = $pdo->prepare("INSERT INTO mytest (jdata) VALUES (?)");
$stmt->execute([$jdata]);

echo pdo2text($pdo, "SELECT * FROM mytest");
echo pdo2text($pdo, "SELECT id
                          , jdata->>'$[0].name' as dog1 
                          , jdata->>'$[1].name' as dog2 
                          , jdata->>'$[2].name' as dog3 
                     FROM mytest");
?>
+----+------+-------+--------+
| id | dog1 | dog2  | dog3   |
+----+------+-------+--------+
| 1  | fido | rover | woofie |
+----+------+-------+--------+

However, that requires that you know how many dogs.

I'd go for 1 dog per row...

+----+-------------------------------------------+
| id | jdata                                     |
+----+-------------------------------------------+
| 1  | {"id": "1", "age": "5", "name": "fido"}   |
| 2  | {"id": "2", "age": "3", "name": "rover"}  |
| 3  | {"id": "3", "age": "1", "name": "woofie"} |
+----+-------------------------------------------+

SELECT id
     , jdata->>'$.name' as dog_name 
FROM mytest
+----+----------+
| id | dog_name |
+----+----------+
| 1  | fido     |
| 2  | rover    |
| 3  | woofie   |
+----+----------+