Mysql concat query

Question

I need concat some column to creat a query, like this

select concat(atributos->'$.listOptions[',valor,'].text') from table

and the final query must be like this

select atributos->'$.listOptions[1].text' from table

but it's returning a string, i don't know if I'm doing something wrong.

this is the content of column atributos

{
  "type": "select",
  "description": "Rota",
  "default": "",
  "required": "0",
  "listOptions": [
    {
      "text": "1 - Jardins",
      "value": "1"
    },
    {
      "text": "2 - Praia do Canto/Shop Vix",
      "value": "2"
    },
    {
      "text": "3 - Hotéis Vitória/Serra",
      "value": "3"
    },
    {
      "text": "6 - Hotéis Vila Velha/Padarias Praia da Costa",
      "value": "6"
    },
    {
      "text": "9 - Cariacica",
      "value": "9"
    },
    {
      "text": "5 - Vitória/Vila Velha",
      "value": "5"
    },
    {
      "text": "10 - Baú/Reboque",
      "value": "10"
    }
  ]
}

Answer 1

concat operates on the values returned for the columns in it's arguments. For example, if you had the table:

id	col	col2
1	Hello	World
2	Foo	Bar

and ran

select concat(col, col2) as x from table

you would get the result

x
HelloWorld
FooBar

There's no way to build a query's syntax dynamically directly in MySQL without a lot of trouble. The best thing to do would be to build the query in the layer of the system that is running the query, rather than try to do it in the SQL code itself. For example, if you were running this query as part of a Python process, you could build the query up as a Python string and then send it to MySQL to run.