PHP mysql Sort JSON data by keys

I want to sort the json data but the sorting shows it wrong. what is the reason of this ? below is the name of my database table I have given pages column name page_content and json data below. Where am I making a mistake? I’m new here. Please be happy if you act accordingly.

Database data page_content

{
    "kind":"video",
    "etag":"edtags",
    "id":"id",
    "statistics":{
        "viewCount":"1434348",
    }
}
{
    "kind":"video",
    "etag":"edtags",
    "id":"id",
    "statistics":{
        "viewCount":"16058462",
    }
}
{
    "kind":"video",
    "etag":"edtags",
    "id":"id",
    "statistics":{
        "viewCount":"2176846",
    }
}
{
    "kind":"video",
    "etag":"edtags",
    "id":"id",
    "statistics":{
        "viewCount":"8088410",
    }
}
{
    "kind":"video",
    "etag":"edtags",
    "id":"id",
    "statistics":{
        "viewCount":"9885758",
    }
}
{
    "kind":"video",
    "etag":"edtags",
    "id":"id",
    "statistics":{
        "viewCount":"26795682",
    }
}
{
    "kind":"video",
    "etag":"edtags",
    "id":"id",
    "statistics":{
        "viewCount":"323810",
    }
}
{
    "kind":"video",
    "etag":"edtags",
    "id":"id",
    "statistics":{
        "viewCount":"529588",
    }
}
{
    "kind":"video",
    "etag":"edtags",
    "id":"id",
    "statistics":{
        "viewCount":"6416534",
    }
}

index.php

$query = $db->query('SELECT * FROM pages order by JSON_EXTRACT(page_content,"$.statistics.viewCount") ASC');
$query->execute();
$row = $query->fetchAll(PDO::FETCH_ASSOC);
if ($row ) {

    foreach ($row as $value) {

        $json = json_decode($value['page_content'], true);
        $videoView = $json['statistics']['viewCount'];

        echo $videoView;

    }
}

print_r

9885758
8088410
6416534
529588
323810
26795682
2176846
16058462
1434348

Answer

You need to unquote the json value, and cast it to a number so you can use it for proper ordering. You can do both at once as follows:

order by page_content ->> '$.statistics.viewCount' + 0

If your version of MySQL does not support operator ->>, then:

order by json_extract(page_content, '$.statistics.viewCount')) + 0

Demo on DB Fiddle – both queries yield:

| page_content                                                                             |
| :--------------------------------------------------------------------------------------- |
| {"id": "id", "etag": "edtags", "kind": "video", "statistics": {"viewCount": "323810"}}   |
| {"id": "id", "etag": "edtags", "kind": "video", "statistics": {"viewCount": "529588"}}   |
| {"id": "id", "etag": "edtags", "kind": "video", "statistics": {"viewCount": "1434348"}}  |
| {"id": "id", "etag": "edtags", "kind": "video", "statistics": {"viewCount": "2176846"}}  |
| {"id": "id", "etag": "edtags", "kind": "video", "statistics": {"viewCount": "6416534"}}  |
| {"id": "id", "etag": "edtags", "kind": "video", "statistics": {"viewCount": "8088410"}}  |
| {"id": "id", "etag": "edtags", "kind": "video", "statistics": {"viewCount": "9885758"}}  |
| {"id": "id", "etag": "edtags", "kind": "video", "statistics": {"viewCount": "16058462"}} |
| {"id": "id", "etag": "edtags", "kind": "video", "statistics": {"viewCount": "26795682"}} |