我想對 json 數據進行排序，但排序顯示錯誤。這是什么原因？下面是我的數據庫表的名稱，我在下面給出了頁面列名稱page_content和 json 數據。我哪里出錯了？我是新來的。如果您采取相應行動，請感到高興。數據庫數據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", }}索引.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; }}打印_r988575880884106416534529588323810267956822176846160584621434348
1 回答
函數式編程
TA貢獻1807條經驗 獲得超9個贊
您需要取消引用 json 值，并將其轉換為數字，以便可以使用它進行正確的排序。您可以同時執行這兩項操作，如下所示：
order by page_content ->> '$.statistics.viewCount' + 0
如果您的 MySQL 版本不支持運算符->>，則：
order by json_extract(page_content, '$.statistics.viewCount')) + 0
DB Fiddle 上的演示- 兩個查詢都會產生：
| 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"}} |
- 1 回答
- 0 關注
- 178 瀏覽
添加回答
舉報
0/150
提交
取消