Consulta por item em JSON

[Guilherme Luiz 41]

Olá pessoal,

 

Estou com uma pequena dúvida em relação a buscar dados em json no MySQL.

No meu banco eu tenho uma table com o seguinte campo em json.

 

table - contacts

column - groups_id

 

Na coluna groups_id eu armazeno ids de grupos em json por exemplo:

["252","222","666"]

 

Quando eu realizo a seguinte query

SELECT * FROM user_contacts WHERE JSON_CONTAINS(group_id, '["252"]')

 

Beleza... Ele me resulta em todos os contatos que possuem o registro 252.

O que acontece é que essa table é 1 > N, ou seja, um contato pode fazer parte de vários grupos, sendo assim:

 

quando eu consulto

SELECT * FROM user_contacts WHERE JSON_CONTAINS(group_id, '["252","222"]')

Ele não me resulta nos contatos que estão com grupo 252 ou 222.

Ele me resulta nos contatos que estão com o grupo 252 e 222, ou seja, ele me retorna somente os contatos que o group id tenha ["252","222"]

 

Eu vi que nessa situação eu poderia criar um INDEX na minha tabela usando:

ALTER TABLE user_contacts ADD INDEX groups ( (CAST(group_id->'$.groups' AS UNSIGNED ARRAY)) );

 

Mas o CAST() na versão que Mysqli que eu possuo no servidor 4.6.X me retorna Syntax error.

Alguem teria uma sugestão ou luz de qual caminho posso seguir nessa situação sem que seja atualizando a versão do bd?

 

 

[Guilherme Luiz 41]

Pessoal,

 

Já consegui "resolver" a minha query com a seguinte consulta:

SELECT * FROM user_contacts WHERE JSON_EXTRACT(group_id, '$') REGEXP '227|222'

Dessa forma ele me resulta em todos os contatos que tenham um e outro ou somente um.

Entretanto se eu buscar por um registro que contenha somente o número 2, ele me resulta em todos os registros com 2. De toda forma essa é a maneira que encontrei no momento.

 

Se alguém tiver alguma outra forma de fazer o mesmo... Por favor, me de essa luz rs

 

Vlw pessoal

[Guilherme Luiz 41]

Pessoal,

 

Agora sim consegui usando um pouco de regex antes de passar os parametros na query:

  $ids   = [];
  foreach($data['ids'] as $id){
    $ids[] = "([[:<:]]|^)".$id."([[:>:]]|$)";
  }
  $ids   = implode("|",$ids);
  $query = "SELECT * FROM user_contacts WHERE JSON_EXTRACT(group_id, '$') REGEXP '{$ids}'";
  $sql   = mysqli_query($connect_sql, $query);

Se alguém tiver alguma outra forma de fazer o mesmo... Por favor, me de essa luz rs

 

Abraço