mysql - 在 MySQL 中按 id 从 JSON 过滤表

Question

我想从 MySQL 中的 JSON 数组中过滤表。示例:

select description from books where id_of_the_book in (jsonarray)

实际上我尝试过这个:

set @test = JSON_ARRAY(JSON_OBJECT('id','1'),JSON_OBJECT('id','2'),JSON_OBJECT('id','3'));

select description from books where id_of_the_book in (select JSON_EXTRACT(@test, '$**.id'))

但是它不会工作。

谢谢

Answer 1

几个选项:

使用13.5 Prepared Statements :

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

使用JSON_TABLE() :

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

参见dbfiddle .