PHP mysql Sort JSON data by keys

Question

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

GMB · Accepted Answer · 2020-07-22 22:22:09Z

1

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"}} |

edited Jul 22, 2020 at 22:22

answered Jul 22, 2020 at 22:12

GMB

224k25 gold badges103 silver badges151 bronze badges

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

8 Comments

Kaan Demir Over a year ago

error Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>> '$.statistics.viewCount' + 0 ASC' at line 1 in \vlog.php:2 Stack trace: #0 \vlog.php(2): PDO->query('SELECT * FROM p...') #1 D:\xampp\htdocs\sonsurum\app\controller\vlog.php(3): require('D:\\xampp\\htdocs...') #2 D:\xampp\htdocs\sonsurum\index.php(34):

GMB Over a year ago

@KaanDemir: it looks like your MySQL version does not support operator ->>. See my updated answer.

Kaan Demir Over a year ago

If there is a word instead of a number, will it continue at the time?

GMB Over a year ago

@KaanDemir: if viewCount is not a number (and does not start with a number), it will be converted to 0, and ordered accordingly.

Kaan Demir Over a year ago

dbfiddle.uk/… can you look here again title

|

Collectives™ on Stack Overflow

PHP mysql Sort JSON data by keys

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related