By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT VERSION();
| VERSION() |
|---|
| 8.0.36 |
DROP TABLE IF EXISTS `books`;
CREATE TABLE IF NOT EXISTS `books` (
`id` BIGINT UNSIGNED NOT NULL PRIMARY KEY,
`description` TEXT
);
INSERT INTO `books`
(`id`, `description`)
VALUES
(1, 'description 1'),
(2, 'description 2'),
(3, 'description 3');
SET @`json` :=
JSON_ARRAY(
JSON_OBJECT('id', 1),
JSON_OBJECT('id', 2),
JSON_OBJECT('id', 3)
);
Records: 3 Duplicates: 0 Warnings: 0
SET @`sql` := CONCAT('
SELECT
`id`,
`description`
FROM
`books`
WHERE
`id` IN (',
(
SELECT
REPLACE(
REPLACE(
JSON_EXTRACT(@`json`, '$**.id'),
']',
''
),
'[',
''
)),
')');
PREPARE `stmt` FROM @`sql`;
EXECUTE `stmt`;
DEALLOCATE PREPARE `stmt`;
Statement prepared
| id | description |
|---|---|
| 1 | description 1 |
| 2 | description 2 |
| 3 | description 3 |
SELECT
`id`,
`description`
FROM
`books`
WHERE
`id` IN (
SELECT
`der`.`_id_of_the_book`
FROM
JSON_TABLE(
@`json`,
'$[*]'
COLUMNS(
`_id_of_the_book` BIGINT UNSIGNED PATH '$.id'
)
) `der`
);
| id | description |
|---|---|
| 1 | description 1 |
| 2 | description 2 |
| 3 | description 3 |