Laravel query json column array

Question

I am trying to access specific data in a json column using a laravel controller, the DB column is called 'figuresinorder'. I want to access the "wants" key but it's not working when there are multiple values stored.

{"wants": ["1"], "trades": ["12,33,234"]} - this works

{"wants": ["1,2,3"], "trades": ["12,33,234"]} - does not work

The query in the controller is as follows:

$figures2 = customtrades::whereJsonContains('figuresinorder->wants', ['1'])->get();

Any help will be greatly received, been stuck on this for longer than I dare to admit.

insomniac22. Are you sure that searhching for ["1"] would return TRUE if the source is ["1,2,3"]. I would guess it would work if you search for ["1,2,3"]. — Toolbox
– Toolbox, Commented Oct 12, 2020 at 13:04
["1,2,3"] is array has one string, your problem is storing data. should be: ["1", "2", "3"] — MotemaddeN
– MotemaddeN, Commented Oct 12, 2020 at 13:09
Sta - the data is stored directly by laravel in that format, I have tried using the format you suggested but this does still not return the required data. Toolbox - When the query works is does return the collection correctly rather than a TRUE statement. I need to be able to search directly for the individual number id's rather than the whole array. — insomniac22
– insomniac22, Commented Oct 12, 2020 at 13:10

STA · Accepted Answer · 2020-10-12 13:24:38Z

2

MySQL & PostgreSQL support whereJsonContains() with multiple values like this way :

customtrades::whereJsonContains('figuresinorder->wants', ['1','2','3'])->get();

For more, see the documentation here

edited Oct 12, 2020 at 13:24

answered Oct 12, 2020 at 13:09

STA

35.3k9 gold badges49 silver badges62 bronze badges

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

7 Comments

Wesley Smith Over a year ago

The OP wants to return any row where wants contains a 1 this doesnt do that

insomniac22 Over a year ago

The way I'm storing the data is incorrect as stated in the first comment. When updating the DB column values I get the required response. Surprising as the stored data is the way laravel is storing data from my session. Thanks for your help Sta.

STA Over a year ago

@Wesley according to the official documentation, you need to sort the dara like the above method

Wesley Smith Over a year ago

@insomniac22 Selecting this as the correct answer is misleading and incorrect. As you stated, the issue was in the way you were storing the data, not in the way you were selecting it

Wesley Smith Over a year ago

@sta Please add a comment along the lines of "You are storing your data incorrectly as a comma separated string instead of an array. If you change your storing to properly store an array of strings, then your original attempt will work as expected" This would be a correct answer if it included that or something similar.

|

Collectives™ on Stack Overflow

Laravel query json column array

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related