Querying MYSQL longtext holding JSON

user_1497418 0
Viewed 1126

I have a table with the following structure:

CREATE TABLE `Event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created` datetime(6) NOT NULL,
  `last_updated` datetime(6) NOT NULL,
  `info` longtext NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=720 DEFAULT CHARSET=latin1

I need to query it using the info column (which holds JSON string).

If I query it doing:

SELECT e.id, e.created, JSON_TYPE(e.info) AS info
  FROM Event e

It returns OBJECT on the INFO column, but if I try to grab an specific column of the JSON, I receive:

SQL Error [3144] [22001]: Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'.

The SQL I am running:

SELECT INFO - > '$User'
  FROM (SELECT e.id, e.created, JSON_TYPE(e.info) AS info
          FROM Event e) as X

Its not an option to change the type of the column to json

mysql json
1 Answers

You can try to CAST to JSON then

SELECT info -> '$.User', info_type
  FROM (SELECT e.id, e.created, 
               JSON_TYPE( CAST( info AS JSON ) ) AS info_type, 
               CAST( info AS JSON ) AS info
          FROM Event e) as X

since a string value within a JSON value accepting function( JSON_TYPE() ) wouldn't work.

Btw, you don't need to bring the JSON type, but the JSON only, for your case.

Update : You can alternatively use without subquery by using JSON_EXTRACT() as

SELECT JSON_EXTRACT( CAST( info AS JSON ) , '$.A' ) AS info,
       JSON_TYPE( CAST( info AS JSON ) ) AS info_type
  FROM Event
user_58413060
Related