Jump to content

MySQL JSON Field Type Indexed Array


Recommended Posts

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?

Link to post
Share on other sites

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            |
+----+----------------+

 

Link to post
Share on other sites

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...

Link to post
Share on other sites

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   |
+----+----------+

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.