How to extract json data in mysql select?

Question

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?

Provide sample data as INSERT INTO and desired output for it. — Akina
– Akina, Commented Nov 9, 2022 at 10:42

Valeriu Ciuca · Accepted Answer · 2022-11-09 12:20:37Z

2

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

edited Nov 9, 2022 at 12:20

answered Nov 9, 2022 at 11:02

Valeriu Ciuca

2,10413 silver badges14 bronze badges

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

6 Comments

Akina Over a year ago

In MySQL 5.7 there exists JSON datatype - what is the reason for LONGTEXT using? And you do not need to quote doublequote chars in the value.

Valeriu Ciuca Over a year ago

I created the table in NAVICAT for testing and this is how he created the table definition DDL when I chose the field type JSON. On localhost I have 10.4.24-MariaDB. JSON is converted in longtext -> mariadb.com/kb/en/json-data-type

Akina Over a year ago

But then you copy this to fiddle - you'd fix this. Additionally - you create fiddle using version 8.0. On version 5.7, your CHECK constraint will be cut out of the code - this version parses but ignores it.

Akina Over a year ago

On localhost I have 10.4.24-MariaDB. Yes, MariaDB have not implemented JSON datatype yet.

membersound Over a year ago

You're correct: on empty databases the sql statement works without problems (of course without results). So it somehow has to do with the data inside, not the statement itself.

|

membersound · Accepted Answer · 2022-11-09 12:48:30Z

0

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, '"'))
...

answered Nov 9, 2022 at 12:48

membersound

87.9k222 gold badges690 silver badges1.2k bronze badges

Collectives™ on Stack Overflow

How to extract json data in mysql select?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related