Extract column text from a table

Question

Extract column text from a table

Navigation

#1 by (1 votes)

1

I have the following table structure:

Themetadatafieldisliketexttype,butIbelieveit'sactuallyaJSON.

WhenIdo:

SELECTmetadataFROMmaxpay.mp_pay_orders;

Theresultis:

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

I need to extract the idtransactions of this field in a way that I can query it 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. By the answers, I have already seen that it is not JSON, since it is only available from version 5.7.8. But I can not extract this information from this text in any way.

mysql

asked by anonymous 24.09.2018 / 12:52

1 answer

Help with an associative table of friends How do I join results from different foreach in an array?

score 1 · Accepted Answer

From MySQL 5.7.8 you can use columns of type JSON .

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

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

If your server does not support JSON columns you can use a REGEX to extract an 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 example above, the regex is being used to make INNER JOIN with the maxpay.transactions table using the idtransactions field of a JSON.

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