How to extract json data in mysql select?-CodePudding

I have two tables, and I want to use a value from primary table (here: person.ref) to extract some data from a json field in a secondary table (here: person_details):

create table person ( 
    id int(8),
    ref varchar(20),
    primary key (id)
);

create table person_details (
    id int(8),
    details json,
    primary key (id)
);

SELECT JSON_EXTRACT(details, CONCAT('$.', p.ref))
FROM person p
JOIN person_details d using (id);

The person_details data is like:

{
  "ref1": {
    ...
  }
}

But that does not matter, because the sql statement itself seems to be invalid:

Result:

#3143 - Invalid JSON path expression. The error is around character position 12.

Why?

CodePudding user response：

Your code should work on MySQL >= 5.7. Check this dbfiddle:

https://dbfiddle.uk/esEJ9uHd

CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ref` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `person_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `details` json,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `person` (`id`, `ref`) VALUES (1, 'ref1');
INSERT INTO `person` (`id`, `ref`) VALUES (2, 'ref1');
INSERT INTO `person_details` (`id`, `details`) VALUES (1, '{"ref1":  {"name": "Barney"}}');
INSERT INTO `person_details` (`id`, `details`) VALUES (2, '{"ref1":  {"name": "Willma"}}');

SELECT JSON_EXTRACT(details, CONCAT('$.', p.ref))
FROM person p
JOIN person_details d using (id);

CodePudding user response：

While the answer above is correct, it did not solve my issue, but I had to add doublequotes around p.ref, so that the concatenation results in CONCAT($."p.ref"):

SELECT JSON_EXTRACT(details, CONCAT('$."', p.ref, '"'))
...