18

I am storing destinations field as json type in mysql

example column value ["Goa", "Moonar", "Kochi"]

I would like to get all rows that matches goa as destinations

However this row query returns the desired result 

SELECT * FROM `packages` 
WHERE JSON_CONTAINS(destinations, '["Goa"]');

But what is the eloquent equivalent of the above query??

Laravel version 5.3

Modelname :Search

shamon shamsudeen
  • 5,466
  • 17
  • 64
  • 129

2 Answers2

25

Probably forced to use a partially raw query like:

use DB; (top of file definition)

DB::table('packages')->whereRaw('json_contains(destinations, \'["Goa"]\')')->get();

And if you have a model:

Package::whereRaw('json_contains(destinations, \'["' . $keyword . '"]\')')->get();

assuming your query above works in SQL.

Jonathan
  • 10,936
  • 8
  • 64
  • 79
  • How do i replace `Goa` with a variable like this `$keyword='goa'` – shamon shamsudeen Jan 30 '17 at 18:06
  • Just use string concatenation I suppose? – Jonathan Jan 30 '17 at 18:07
  • Got an error in sql syntax `Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 (SQL: select * from `finde_packages` where json_contains(destinations, '["Goa"]')` – shamon shamsudeen Jan 30 '17 at 18:09
  • Got it its a typo missing `)` at the end of `json_contains`. One more question how do i remove case senstivity of the keyword?? – shamon shamsudeen Jan 30 '17 at 18:15
  • Again a simple mistake `whereraw` always returns one row if keyword matches more than one row – shamon shamsudeen Jan 30 '17 at 18:21
  • https://github.com/laravel/framework/blob/5.3/src/Illuminate/Database/Query/Builder.php#L652 – Jonathan Jan 30 '17 at 18:22
  • https://laracasts.com/discuss/channels/eloquent/json-where-clauses if you wanted it to be in the select, use `->selectRaw('...')` or `->select(DB::raw('...'))` – Jonathan Jan 30 '17 at 18:24
  • Its the problem with keyword case sensitive keyword – shamon shamsudeen Jan 30 '17 at 18:26
  • Good luck! I think this is starting to go off-topic from the original question now. I don't think you can search in a case insensitive way using this function. You would need to use a `where('x', 'like', '%z%')` or similar to get around the problem of case or maybe it's as simple as this http://stackoverflow.com/questions/3936967/mysql-case-insensitive-select – Jonathan Jan 30 '17 at 18:27
  • @Jonathan what will be the opposite of json_contains. As, in this example, I want to get all rows whose destinations does not contain [GOA] – Saroj Shrestha Jul 15 '18 at 08:11
  • `not json_contains` – Jonathan Jul 19 '21 at 16:21
20

In Laravel 5.6 and higher you can use whereJsonContains method:

Package::whereJsonContains('destinations',["Goa"])->get();

But not all DB support it: https://laravel.com/docs/5.6/queries#json-where-clauses

Anton Ganichev
  • 2,184
  • 1
  • 18
  • 17
  • Laravel supports querying JSON column since Laravel 5.2. [Docs](https://laravel.com/docs/5.2/queries#json-where-clauses). – ibnɘꟻ May 17 '21 at 07:19