SQL query JSON of MySQL 8.0 comes String instead of Array

Question

I create a table in MySQL 8.0 as follows:

CREATE TABLE `airline_table` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`info` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

It contains JSON type data, and I insert some data as:

INSERT INTO airline_table VALUES ('1','{"data": [{"city": "Houston", "state": "TX"}, 
      {"city": "Los Angles", "state": "CA"}], "airline": ["UA", "AA"]}');

And I use php to visit the database, I wish to get value of "airline" as an Array.

<?php

$mysqli = new mysqli("localhost", "root", "aproot2019", "test");
$sql = "SELECT id, info -> '$.airline' AS airline FROM airline_table";

$result = $mysqli->query($sql);
$row = $result->fetch_array();
//print_r($row);
$airline = $row['airline'];

echo $airline . "<br>";   //  ["UA", "AA"] , this is a string but not an array, how can I have an Array? 
echo is_array($airline) ? 'Array' : 'not an Array'     . "<br>";      // not an Array
echo is_string($airline) ? 'String' : 'not a String'   . "<br>" ;    // String

$mysqli->close();

?>

But it comes out a String, NOT an Array! This really annoys me, and JSON in MySQL is hard to understand.

You don't have to use JSON (unless this is an exercise) and the data would be much more reliable with a well designed database with referential integrity etc. — Nigel Ren
– Nigel Ren, Commented Apr 9, 2022 at 15:22
Seems like bad database design, and also you are not bound to use the mysql returned data as is, you can json_decode that column then its easier to understand — Ali Rahimi
– Ali Rahimi, Commented Apr 9, 2022 at 15:29
I have to use JSON, because others designed the database structure, and heavily used JSON. — John
– John, Commented Apr 9, 2022 at 15:35

Manish Kumar · Accepted Answer · 2022-04-09 15:36:03Z

3

Have you considered decoding JSON?

$json = json_decode('{"data": [{"city": "Houston", "state": "TX"}, {"city": "Los Angles", "state": "CA"}], "airline": ["UA", "AA"]}');

// for your case it will be :
// $json = json_decode($row['airline']);
echo var_dump($json->airline);
/**
 * array(2) {
  [0]=>
  string(2) "UA"
  [1]=>
  string(2) "AA"
}

answered Apr 9, 2022 at 15:36

Manish Kumar

6343 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Luuk · Accepted Answer · 2022-04-09 15:54:35Z

2

Querying a JSON from MySQL is not hard, but.... For this kind of tables I would not do it in JSON. 😉

SELECT 
   j.city,
   j.state
FROM airline_table
CROSS JOIN JSON_TABLE(info, '$.data[*]' COLUMNS( 
       city VARCHAR(20) PATH '$.city', 
       state VARCHAR(20) PATH '$.state')) as j

output:

city	state
Houston	TX
Los Angles	CA

answered Apr 9, 2022 at 15:54

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Collectives™ on Stack Overflow

SQL query JSON of MySQL 8.0 comes String instead of Array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related