Search code examples
mysqlarraysjsonlaraveljson-extract

Get object inside more than one object MYSQL JSON

How can I select array "1" inside the "flavor" object from json code in mysql

Attribute name: settings

{"without":{"usd":{"new":"5","old":"8"},"weight":"5"},"color":{"2","3"},"flavor":{"1","2"}}

And how can I get a number inside the "usd" object inside "new" knowing these objects are inside the first object and they are variable, perhaps ["without" or "long" or ......]

Attribute name: settings

{"without":{"usd":{"new":"5","old":"8"},"weight":"5"},"color":{"2","3"},"flavor":{["1","2"}}
{"long":{"usd":{"new":"2","old":"3"},"weight":"2"},"medium":{"usd":{"new":"3","old":"4"},"weight":"3"},"short":{"usd":{"new":"4","old":"5"},"weight":"4"}}
{"short":{"usd":{"new":"4","old":"5"},"weight":"2"},"color":{"1","2"}}

LIKE

without = 5
long = 2
short = 4
Solution

  • I rebuilt the data format so that I could extract the required data

    {"size":[{"id":1,"url":"without","weight":"5","price":{"usd":{"new":"5","old":"8"}}}],"color":[{"id":"2","url":"yellow"},{"id":"3","url":"green"}],"flavor":[{"id":"1","url":"berry"},{"id":"2","url":"strawberry"}]}

    MYSQL

    JSON_EXTRACT(details.settings, '$.color[*].url') LIKE '%yellow%'