How to Build a JSON Array from MySQL Database

How to build a JSON array from mysql database

Is something like this what you want to do?

$return_arr = array();

$fetch = mysql_query("SELECT * FROM table"); 

while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {
    $row_array['id'] = $row['id'];
    $row_array['col1'] = $row['col1'];
    $row_array['col2'] = $row['col2'];

    array_push($return_arr,$row_array);
}

echo json_encode($return_arr);

It returns a json string in this format:

[{"id":"1","col1":"col1_value","col2":"col2_value"},{"id":"2","col1":"col1_value","col2":"col2_value"}]

OR something like this:

$year = date('Y');
$month = date('m');

$json_array = array(

//Each array below must be pulled from database
    //1st record
    array(
    'id' => 111,
    'title' => "Event1",
    'start' => "$year-$month-10",
    'url' => "http://yahoo.com/"
),

     //2nd record
     array(
    'id' => 222,
    'title' => "Event2",
    'start' => "$year-$month-20",
    'end' => "$year-$month-22",
    'url' => "http://yahoo.com/"
)

);

echo json_encode($json_array);

How to create multidimensional JSON Array from MYSQL database?

Here how it should be:

while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)) {
    // code without `extract`:

    $latitude = $row['lat'];    
    $longitude = $row['lon'];

    $point1 = array('lat' => number_format ($latitude,4,'.',''), 'long' => number_format ($longitude,4,'.',''));
    $point2 = array('lat' => number_format ($latitude2,4,'.',''), 'long' => number_format ($longitude2,4,'.',''));
    $distance = getDistanceBetweenPointsNew($point1['lat'], $point1['long'], $point2['lat'], $point2['long']);

    $channel = array(
        'id' => $row['id'],
        'title' => $row['title'],
        'about' => $row['about'],
        'address' => $row['address'],
        'lat' => $latitude,
        'lon' => $longitude,
        'distance' => $distance,
    );

    $records[] = $channel;
}
// echo ONCE
// and as `json_encode` returns a string - using '' is USELESS
echo json_encode($records, JSON_UNESCAPED_SLASHES);

Also, as $point2 never changes, it's better to set

$point2 = array('lat' => number_format ($latitude2,4,'.',''), 'long' => number_format ($longitude2,4,'.',''));

outside while loop.

How to convert result table to JSON array in MySQL

New solution:

Built using Your great comments, thanks!

SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'phone', phone)) from Person;

---

Old solution:

With help from @Schwern I managed to put up this query, which seems to work!

SELECT CONCAT(
    '[', 
    GROUP_CONCAT(JSON_OBJECT('name', name, 'phone', phone)),
    ']'
) 
FROM person;

Pulling data from MySQL into json array

If you wanted to do this with PDO then here is an example:

<?php 
$dbh = new PDO("mysql:host=localhost;dbname=DBNAME", $username, $password);

$sql = "SELECT `id`, `title`, `time`, `start`, `backgroundColor` 
        FROM my_table";

$result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
//To output as-is json data result
//header('Content-type: application/json');
//echo json_encode($result);

//Or if you need to edit/manipulate the result before output
$return = [];
foreach ($result as $row) {
    $return[] = [ 
        'id' => $row['id'],
        'title' => $row['title'],
        'start' => $row['start'].' '.$row['time'],
        'backgroundColor' => $row['backgroundColor']
    ];
}
$dbh = null;

header('Content-type: application/json');
echo json_encode($return);
?>

Creating array inside array from mysql data to json

You need to remap your array and then initialize an array for the rights key... so, change your while loop something like this:

$json_response = array();
while($row = $result->fetch_assoc()) {
    if (!isset($json_response[ $row['idCategory'] ])) {
        $json_response[ $row['idCategory'] ] = [
            'idCategory' => $row['idCategory'],
            'nameCategory' => $row['nameCategory'],
            'rights' => [],
        ];
    }
    $json_response[ $row['idCategory'] ]['rights'][] = [
        'idRight' => $row['rid'],
        'name' => $row['rname'],
        'price' => $row['price'],
        'image' => $row['rimg']
    ];
}

// We want the final result to ignore the keys and to create a JSON array not a JSON object 
$data = [];
foreach ($json_response as $element) {
    $data[] = $element;
}

echo json_encode($data);

This part of the code $json_response[ $row_array['idCategory'] ] helps to maintain a unique grouping of the data because it creates a hash based on the idCategory. An array can only have one key and since idCategory is always unique we can use that as the key for grouping on.
Then because we now have a hash-based array, we have to create a new array that is a 'real' array for when it is converted to JSON.
You do not want to use GROUP BY or GROUP_CONCAT in this situation.

How to convert JSON object rows into JSON array in MySQL?

You can use MySQL JSON_ARRAYAGG aggregation function:

SELECT JSON_ARRAY_AGG(JSON_OBJECT('uniqueId', uniqueId, 'name', name)) actors
FROM (
select stf.id as familyId, stl.skill_type_name  as name
from actor_family af, actor_layered al
where af.id = al.actor_family_id) AS actors
GROUP BY uniqueId;

Try it here.

Creating JSON Array out of PHP loop

Add a new associative array to the data list instead of only adding the values to your flat array:

$data = [];
for (..) {
    // minimal example - more code to populate $date and $distance here
    $data[] = [
        'training_date' => $date,
        'distance' => $total_distance
    ];
}
echo json_encode($data);

Also, as already suggested in the comments, SQL can summarize the distances for you by using GROUP BY and SUM() in your query:

SELECT user_id, training_date, training_art_id, SUM(distance) AS total_distance
FROM training 
    INNER JOIN training_content 
        ON training.training_id = training_content.training_id
WHERE training.user_id = ?
    AND training.training_art_id != ?
    AND training.training_date BETWEEN ? AND (? - INTERVAL 1 MONTH)
GROUP BY user_id, training_date, training_art_id

Note that i also changed the training_date condition to a 1 month range, because your desired example output contains more than a single date.

You can also build a SUM over multiple fields:

SELECT ..., (SUM(warmup) + SUM(cooldown) + SUM(..)) AS total_distance

When you query the database like above, the resulting recordset already has the form you want to output, so it could be as easy as:

$trainings = $get_user_trainings->get_result();
echo json_encode($trainings);

How to create an index on a JSON array using MySQL 8

There are 2 separate problems.

---

First is solvable. The next fiddle describes it:

CREATE TABLE mytable (metadata JSON);
INSERT INTO mytable VALUES ('[{"key": "apples", "string_values": ["red"]}, {"key": "oranges", "string_values": ["orange"]}]');

-- you try to index arrays
SELECT metadata->'$[*].string_values' FROM mytable;
-- but you need in scalar values
SELECT metadata->'$[*].string_values[0]' FROM mytable;

| metadata->'$[*].string_values' |
| :----------------------------- |
| [["red"], ["orange"]]          |

| metadata->'$[*].string_values[0]' |
| :-------------------------------- |
| ["red", "orange"]                 |

db<>fiddle here

---

Second problem is unsolvable.

Look carefully to the statement which you use when you try to create index:

ALTER TABLE mytable ADD INDEX myindex((CAST(metadata->'$[*].string_values' AS UNSIGNED ARRAY)));

The values in the array ["red", "orange"] are NOT numeric.

---

Related Topics