Laravel 5.6. JSONb column. Count array

Question

I'm using JSONb columns of the PostgreSQL.

I have such an array in my data column

{
    myKey: []
}

How can I check through Laravel, if this array is empty or not?

Something like

MyModel::where('data->myKey'...)

You should check this postgresql.org/docs/9.3/static/functions-json.html . json_array_length might be helpful — berkay kılıç
– berkay kılıç, Commented Jun 12, 2018 at 21:05

Jonas Staudenmeir · Accepted Answer · 2018-09-06 21:39:20Z

1

You have to use a raw statement:

MyModel::whereRaw("json_array_length((data->'myKey')::json) > 0")

In Laravel 5.7.2 you can use whereJsonLength():

MyModel::whereJsonLength('data->myKey', '>', 0)

answered Jun 12, 2018 at 23:44

Jonas Staudenmeir

26.1k7 gold badges70 silver badges115 bronze badges

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

1 Answer 1