0

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
Makarious saad
  • 21
  • 1
  • 5
  • 1
    These are not *arrays*, but *objects* that are nested within the top object (json arrays are delimited by `[]`, and objects by `{}`). Objects keys have no particular order, so there is no notion of "first" key. This makes your requirement unclear. – GMB Jun 13 '20 at 14:23
  • @GMB I changed from an array to an object – Makarious saad Jun 13 '20 at 14:56
  • OK. But there is no "first" object. JSON keys are unordered. What you are seeing is just a representation of the data, which is not guaranteed to be consistent. – GMB Jun 13 '20 at 14:59

1 Answers1

1

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%'
GMB
  • 216,147
  • 25
  • 84
  • 135
Makarious saad
  • 21
  • 1
  • 5