时间戳

首页 > 解决方案 > 如何在mysql和laravel中查询json对象数组

laravel - 如何在mysql和laravel中查询json对象数组

问题描述

我有一个包含以下列的数据库表：

CREATE TABLE sales
(
    id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
    invoice_id int unsigned,
    tickets json,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at timestamp
);

现在我在表中有以下数据：

INSERT INTO sales (id, invoice_id, tickets, created_at, updated_at) VALUES (3, 1, '[{"id": 3, "code": "FA82-540E", "date": "2018-11-17", "seat": "4", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-15 17:37:39", "pickup_point": "16:15:00"}, {"id": 4, "code": "C3B6-B3E6", "date": "2018-11-17", "seat": "4", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-15 18:27:21", "pickup_point": "16:15:00"}, {"id": 5, "code": "3949-6418", "date": "2018-11-17", "seat": "4", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-15 18:33:35", "pickup_point": "16:15:00"}, {"id": 6, "code": "FC67-9EC9", "date": "2018-11-17", "seat": "4", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-15 18:36:52", "pickup_point": "16:15:00"}, {"id": 7, "code": "CEAA-1AF4", "date": "2018-11-17", "seat": "20", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-15 19:21:40", "pickup_point": "16:15:00"}, {"id": 8, "code": "B3DB-CB40", "date": "2018-11-17", "seat": "12", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-15 19:21:40", "pickup_point": "16:15:00"}, {"id": 9, "code": "1EC8-E336", "date": "2018-11-17", "seat": "20", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-15 19:23:16", "pickup_point": "16:15:00"}, {"id": 10, "code": "DD6C-4177", "date": "2018-11-17", "seat": "12", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-15 19:23:16", "pickup_point": "16:15:00"}, {"id": 11, "code": "96E7-99E6", "date": "2018-11-17", "seat": "17", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-15 19:23:16", "pickup_point": "16:15:00"}, {"id": 13, "code": "BBFF-081F", "date": "2018-11-17", "seat": "6", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-15 19:51:10", "pickup_point": "16:15:00"}, {"id": 15, "code": "C45D-4EE4", "date": "2018-11-17", "seat": "1", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-16 14:45:06", "pickup_point": "16:15:00"}, {"id": 16, "code": "FF50-E7A6", "date": "2018-11-17", "seat": "4", "time": "01:15:00", "class": "VIP", "price": "170.00", "created_at": "2018-11-16 15:14:48", "pickup_point": "01:15:00"}, {"id": 18, "code": "BE2C-8A6B", "date": "2018-11-17", "seat": "1", "time": "01:15:00", "class": "VIP", "price": "170.00", "created_at": "2018-11-16 15:32:29", "pickup_point": "01:15:00"}, {"id": 19, "code": "0F9A-E729", "date": "2018-11-17", "seat": "2", "time": "01:15:00", "class": "VIP", "price": "170.00", "created_at": "2018-11-16 15:41:31", "pickup_point": "01:15:00"}, {"id": 20, "code": "8F3B-3FEE", "date": "2018-11-17", "seat": "1", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-16 16:44:07", "pickup_point": "988 Ritchie Cove\\nSouth Draketown, SD 48053"}, {"id": 2820, "code": "E0E4-CA78", "date": "2019-01-13", "seat": null, "time": "02:36:45", "class": "Regular", "price": "212.31", "venue": "Bartoletti Road", "created_at": "2018-11-16 18:18:59"}, {"id": 2821, "code": "1E4B-6BCE", "date": "2019-01-13", "seat": null, "time": "02:36:45", "class": "Regular", "price": "212.31", "venue": "Bartoletti Road", "created_at": "2018-11-16 18:31:05"}, {"id": 2822, "code": "1CF8-6AB1", "date": "2018-11-17", "seat": "2", "time": "16:15:00", "class": "Regular", "price": "156.55", "created_at": "2018-11-16 18:35:56", "pickup_point": "988 Ritchie Cove\\nSouth Draketown, SD 48053"}, {"id": 2823, "code": "FE6D-D383", "date": "2019-01-13", "seat": null, "time": "02:36:45", "class": "Regular", "price": "212.31", "venue": "Bartoletti Road", "created_at": "2018-11-16 19:51:57"}, {"id": 2824, "code": "ECAB-223E", "date": "2019-01-13", "seat": null, "time": "02:36:45", "class": "Regular", "price": "212.31", "venue": "Bartoletti Road", "created_at": "2018-11-16 19:51:57"}, {"id": 2825, "code": "5690-BB4D", "date": "2019-01-13", "seat": null, "time": "02:36:45", "class": "VIP", "price": "134.81", "venue": "Bartoletti Road", "created_at": "2018-11-16 19:51:57"}, {"id": 2826, "code": "609E-C02F", "date": "2019-01-13", "seat": null, "time": "02:36:45", "class": "Regular", "price": "212.31", "venue": "Bartoletti Road", "created_at": "2018-11-16 19:54:33"}, {"id": 2827, "code": "F2F8-0184", "date": "2019-01-13", "seat": null, "time": "02:36:45", "class": "Regular", "price": "212.31", "venue": "Bartoletti Road", "created_at": "2018-11-16 19:54:33"}, {"id": 2828, "code": "7502-326A", "date": "2019-01-13", "seat": null, "time": "02:36:45", "class": "VIP", "price": "134.81", "venue": "Bartoletti Road", "created_at": "2018-11-16 19:54:33"}, {"id": 2829, "code": "E6FB-B8FB", "date": "2019-01-13", "seat": null, "time": "02:36:45", "class": "Regular", "price": "212.31", "venue": "Bartoletti Road", "created_at": "2018-11-16 21:34:46"}, {"id": 2830, "code": "8B62-BE0E", "date": "2019-01-13", "seat": null, "time": "02:36:45", "class": "Regular", "price": "212.31", "venue": "Bartoletti Road", "created_at": "2018-11-16 21:34:46"}, {"id": 2831, "code": "4F10-64D9", "date": "2019-01-13", "seat": null, "time": "02:36:45", "class": "VIP", "price": "134.81", "venue": "Bartoletti Road", "created_at": "2018-11-16 21:34:46"}]', '2018-11-15 17:37:39', '2018-11-15 17:37:42');

现在，当我尝试运行以下查询时：

SELECT * FROM sales WHERE JSON_EXTRACT(`tickets` , '$.id') > 1

不返回任何行。在此之前，我尝试用 laravel 做到这一点：

$tickets = Sales::where("tickets->id", ">", 1)->get();

那也行不通。在这一点上，我已经阅读了文档，但是我很困惑该怎么做。

标签： laravellaravel-5mysql-8.0

解决方案

The reason your query doesn't work is because it's trying to extract the id attribute from the root of the tickets object which doesn't exist as the column is storing an array of objects.

Instead you should be able to get the results you desire with 

SELECT * FROM sales WHERE JSON_EXTRACT(`tickets` , '$[*].id') > 1

or

Sales::whereRaw('JSON_EXTRACT(`tickets` , "$[*].id") > ?', [1])->get()

At this point I don't think there is a specific method to use to do this in Laravel.

All that being said, I would definitely recommend the approach in @Chris Linnenkohl's answer and create a separate table for your tickets and then you can just use a hasMany relationship instead.

推荐阅读