The data type returned by MySQL 8.0's SQL query JSON is string instead of array

Question

I created a table in MySQL 8.0 as shown below:

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, I inserted some data as follows:

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

I use php to access the database and hope to get the 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 instead of an array, how can I get 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 outputs a string, not an array! This is really annoying, JSON in MySQL is hard to understand.

Answer 1

Have you considered decoding the JSON?

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

// 对于您的情况，将是：
// $json = json_decode($row['airline']);
echo var_dump($json->airline);
/**
 * array(2) {
  [0]=>
  string(2) "UA"
  [1]=>
  string(2) "AA"
}