Extract column text from a table

Question

I have the following table structure:

The field metadata is like text type, but I believe it is actually a JSON.

When I do:

SELECT metadata FROM  maxpay.mp_pay_orders;

The result is:

{"idtransactions":122882,"transação":122882}

I need to extract the idtransactions of this field, in a way that I can consult you with a IN. Example:

SELECT idtransactions from tabela1 where idtransactions in
    (select metadata??? from maxpay.mp_pay_orders)

The Mysql version of the server is 5.6. From the answers, I have seen that it is not JSON, since it is only available from version 5.7.8. But I can’t extract that information from that text in any way.

Answer 1

From Mysql 5.7.8 you can use columns of the JSON type.

Then you could use one of the available functions to make his SELECT, as the function JSON_EXTRACT for example:

SELECT
    *,
    JSON_EXTRACT('metadata', '$.idtransactions')
FROM maxpay.mp_pay_orders;

---

If your server does not support JSON columns you can use one REGEX to extract information from the string. Ex.:

SELECT
    *,
    metadata REGEXP '"idtransactions":\\d+'
FROM maxpay.mp_pay_orders as po
INNER JOIN maxpay.transactions as t
    ON po.metadata REGEXP CONCAT('"idtransactions":', t.id, '\\D')
;

In the above example the regex is being used to do the INNER JOIN with the table maxpay.transactions using the field idtransactions of a JSON.

I use the CONCAT to create a regex for each comparison made with the table transactions. The \\D at the end is for a id as 123 do not also marry 12300. This way we limit only to the exact id