The table in the category field contains the string:

{"data":[{"key":20517,"children":[{"key":20764,"children":[{"key":20766,"children":[{"key":20770},{"key":22948},{"key":23014}]}]}]}]}

I know that this is json. But I need to find a SQL query among many similar lines, only that line where after the last occurrence of children there is, for example, a substring - 22948 Tell me, please, how to do this?

  • 2
    ru.stackoverflow.com/search?q=%5Bmysql%5D+json - Visman
  • @Visman Does not help. The task cannot be solved within a single request, processing the data as JSON. Externally, the data looks like nested without any restrictions on the level of nesting - and MySQL has problems with recursive queries. I understand that it is possible to make a pseudo table for a trillion - but this is a crook of the curve, which does not formally change things. - Akina

1 answer 1

 WHERE INSTR(category, '"children"') AND INSTR(SUBSTRING_INDEX(category, '"children"', -1), '22948')

Regular here is not needed.