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
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