0

I have table with field related_table_ids (example value ["1110663703","1958153258"]). Tried join related table by query -

SELECT * 
FROM db.table 
INNER JOIN related_table ON related_table.id IN (
  SUBSTRING(table.related_table_ids, 2, (LENGTH(table.related_table_ids) - 2))
)

But that doesn't work, returns 0 rows. help pls!

table

id | related_table_ids
---------
1  | ["1110663703","1958153258"]
2  | ["2032453865"]
3  | ["1304031696"]

related_table

id 
---------
1110663703
1958153258
2032453865
1304031696
id'7238
  • 2,428
  • 1
  • 3
  • 11
Rykers
  • 3
  • 3
  • Provide CREATE TABLE script for `table`. Does `related_table_ids` column is JSON type column? – Akina Oct 26 '21 at 06:22
  • yes, column is json. [dbfiddle](https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=bc91e0bebb60b375443a89c9d64d2d5e) – Rykers Oct 26 '21 at 06:57
  • There is a problem. The values in JSON array are strings whereas the values in related_table are integers. So you cannot search the array for the value, the datatype won't be converted rather then in regular compare, the values from related_table must be converted to strings and wrapped with dquote chars which will degrade the performance. I recommend you to convert the JSON array elements to numbers (remove all dquote chars) which will allow direct compare in JSON_CONTAINS. https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=9a052ecf7ed72640517c20993b40982a – Akina Oct 26 '21 at 07:17
  • Anycase this will be slow due to cartesian - MariaDB does not implement JSON datatype, and it have no multi-valued indexes. Also MariaDB have no JSON_TABLE function. You may test the next - parse JSON array to single values in recursive CTE then join - this method may use index by `related_table (id)` at least... – Akina Oct 26 '21 at 07:20

1 Answers1

1

related_table_ids looks like a json string. Therefore, you can use the JSON_CONTAINS function.

SELECT *
FROM mytable t
JOIN related_table rt ON JSON_CONTAINS(related_table_ids, CONCAT('"', rt.id, '"'))

db<>fiddle

id'7238
  • 2,428
  • 1
  • 3
  • 11
  • That seems like should work, but i get infinity processing of query. dbfiddle is correct. – Rykers Oct 26 '21 at 06:54
  • This is not infinity processing, just many rows. But in migration i get error - "General error: 4037 Unexpected end of JSON text in argument 1 to function 'json_contains'"
    query - `UPDATE table JOIN related_table ON JSON_CONTAINS(table.related_table_ids, CONCAT('\"', related_table.id, '\"')) SET table.field = related_table.field`;     – Rykers Oct 26 '21 at 07:15
  • Thank you! There was fields with value - ''. I resolved that by `CASE WHEN related_table_ids = '' THEN '[]' ELSE related_table_ids END`. – Rykers Oct 26 '21 at 07:28