MySQL Extract JSON from select query with two relational tables

Question

Hi I am working on a Select Query that combines two relational tables with customer information. The field called 'options' contains a lot of JSON information.

I found a similar article however I am unable to make it work within the context of the existing query and the server gives an error message function 'JSON_EXTRACT' does not exist. Upon further review we discovered that the server itself does not have the JSON_EXTRACT function.

Is it possible to simply use % % between two strings?

i.e. select between {"TransactionID":" and "}

just interpreting the JSON ourselves rather than relying on a server function.

Here is the query

select u.Email, u.Name, up.Type, up.Name, up.Price, up.options from users_packages 
inner join users u on up.UserID = u.id

The options field reveals this value for example:

{"TransactionID":"4G631007P6080114Y39840"}

I'm looking for a query that will give me only the value i.e. '4G631007P6080114Y39840' and extract the JSON of the TransactionID. Note, some of the other json elements may be there other than TransactionID

Thanks!

See https://stackoverflow.com/questions/49063684/mysql-extract-json-element-from-array

Which version of MySQL are you running (select version())? — GMB
– GMB, Commented Apr 6, 2020 at 22:02
JSON support was added in MySQL 5.7, so json functions such as JSON_EXTRACT() are not available in 5.5. If you want to work with JSON, you should really consider upgrading (besides, 5.5 is a really old version, which is out of support). — GMB
– GMB, Commented Apr 6, 2020 at 22:09
I understand. Is there a workaround that doesn't involve upgrading the MySQL version on that server? perhaps thru strings? — Viktor
– Viktor, Commented Apr 6, 2020 at 22:14
We upgraded to Maria DB 10.1 the JSON_EXTRACT function does not exist there either. — Viktor
– Viktor, Commented Apr 7, 2020 at 5:37

MajorMarcin · Accepted Answer · 2020-04-06 22:13:56Z

1

try something like this JSON_EXTRACT(yourJSON, "$.fieldname")

select u.Email, u.Name, up.Type, up.Name, up.Price, JSON_EXTRACT(up.options, "$.TransactionID") from users_packages inner join users u on up.UserID = u.id

here you have how json_extract works https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

answered Apr 6, 2020 at 22:13

MajorMarcin

194 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Extract JSON from select query with two relational tables

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related