CODEWITHSUNDEEP
mysqlarraysjson
myself
myself

Reputation: 89

Filter a table by id from JSON in MySQL

i would like to filter a table from a JSON Array in MySQL. Example:

select description from books where id_of_the_book in (jsonarray)

Actually i tried this:

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

but it wont work.

Thank you

Upvotes: 0

Views: 274

Answers (2)

wchiquito
wchiquito

Reputation: 16569

A couple of options:

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

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

See dbfiddle.

Upvotes: 1

myself
myself

Reputation: 89

Here we go, found a solution

drop procedure if exists Sp_ConvertJsonToTable;
create procedure Sp_ConvertJsonToTable()
begin


 declare jsonTest json default JSON_ARRAY(JSON_OBJECT('idcapitolo','1'),JSON_OBJECT('idcapitolo','2'),JSON_OBJECT('idcapitolo','3'));
 declare lunghezzaJson int default json_length(jsonTest);


declare contatore int default 0;


 drop temporary table if exists JsonToTableIds;
    create temporary table JsonToTableIds (
        valoriUtili int
    );




while contatore < lunghezzaJson do

    set @JsonSearch = concat('$[',contatore,'].idcapitolo');
    insert into JsonToTableIds(valoriUtili) values ((select json_unquote(json_extract(jsonTest,@JsonSearch))));
    set contatore = contatore + 1;
    end while;




-- select * from JsonToTableIds;
select descrizionebreve from capitolo where idcapitolo in((select * from JsonToTableIds));
drop temporary table JsonToTableIds;


end;

Upvotes: 0

Related Questions