How to retrieve empty json array by Eloquent?

Question

I have table item which has a JSON column sku_purchase_price. There are some rows like that:

I tried to retrieve those rows from DB by Eloquent:

$items = Item::where('sku_purchase_price', '[]')->get();

but it returns a collection with zero results.

So, how to retrieve those row from DB by Eloquent?

you can convert the data in 'sku_purchase_price' to strings so you retrieve strings, but why do you have [] in the rows — Schnecke
– Schnecke, Commented Aug 2, 2017 at 2:15

alexandre-rousseau · Accepted Answer · 2019-02-26 15:41:06Z

9

You can use Illuminate\Database\Query\Builder::whereJsonLength like this:

$items = Item::whereJsonLength('sku_purchase_price', 0)->get();

This works only with MySQL 5.7, PostgreSQL, SQL Server 2016, and SQLite 3.9.0 (with the JSON1 extension).

answered Feb 26, 2019 at 15:41

alexandre-rousseau

3,0251 gold badge32 silver badges35 bronze badges

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

Comments

giolliano sulit · Accepted Answer · 2017-08-02 02:53:03Z

3

Um there is something not quite right in your code base earlier on if it's inserting empty arrays.

You need to find the code that is inserting into this table and check if there is an empty array being created and if there is, pass through an empty string or nothing if it's not a required field.

However if you want to select them using Eloquent you would do:

$items = Item::where('sku_purchase_price', '\[\]')->get();

But yes, don't do this, find the actual problem in your code first rather than trying to select empty arrays from the db...

answered Aug 2, 2017 at 2:53

giolliano sulit

1,0061 gold badge7 silver badges11 bronze badges

2 Comments

shazim ali Over a year ago

what should i do if i need to get empty downloads data only? $patternsRelated->whereRaw('download != "" AND download != "[]"')->orderBy($sortby)->get();

shazim ali Over a year ago

i am using the code mention in above comment but still i am getting empty download data.

Collectives™ on Stack Overflow

How to retrieve empty json array by Eloquent?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related