How to query json array of objects in mysql and laravel

Question

I have a database table with the following columns:

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
);

Now I have the following data in the table:

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');

Now when I try to run the following query:

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

No rows are returned. Before this I tried to do it with laravel:

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

That doesn't work either. At this point I've read the docs already bbut I'm very confused about what to do.

How come you have you tickets in a field rather than a separate table? — Rwd
– Rwd, Commented Nov 17, 2018 at 16:55

Rwd · Accepted Answer · 2018-11-17 17:26:24Z

2

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.

answered Nov 17, 2018 at 17:26

Rwd

35.3k7 gold badges71 silver badges85 bronze badges

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

2 Comments

user3718908x100 Over a year ago

Hi thanks, this is the answer. I have also created a separate table for tickets however because I have different types of tickets and the columns are different I've decided to use json to store the object.

Nick Poulos Over a year ago

is there an equivalent for postgres?

Collectives™ on Stack Overflow

How to query json array of objects in mysql and laravel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related