CODEWITHSUNDEEP
jsonmariadb
Stephen York
Stephen York

Reputation: 1429

MariaDB 10.6.4 JSON_TABLE function

I'm running a game server and want to offload some processing which is poorly programmed as potentially thousands of separate update statements into a single update with a join, wrapped in a stored procedure. I'm passing a JSON array of unique identifiers as the parameter and want to use the new MariaDB 10.6 JSON_TABLE function to quickly map to an in memory table which can then be used to join onto other tables to get the job done. I've tested the code below with just the @json string and I can see the SELECT * FROM json_table call work fine, I get a single column two row result set. When I do the join onto users table though I'm getting a collation mixing error.

SET @json='[ "license:4b5ef761bfcc3be1e86c3bf16e33f5432417ca6b", "license:4faca2e6f3fe74881e5fb91c9a02bbcfe1d3bb83" ]';

SELECT accounts
FROM users u
INNER JOIN
(
  SELECT * FROM json_table(@json, '$[*]'
    COLUMNS(
      identifier varchar(100) PATH '$' 
    )
  ) AS t1
) AS players
  ON u.identifier = players.identifier;

Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

My database has utf8mb4 charset with collation of utf8mb4_unicode_520_ci and my.ini has those setup as defaults. I would have thought that the system would default to that even in expressions like this, but alas. I can't find anything about setting this within the json_table COLUMNS definition and have tried a few combinations as you would when create table DDL but it's not making any difference.

Does anyone know how to get this to work?

Upvotes: 2

Views: 1000

Answers (1)

nbk
nbk

Reputation: 49373

You need to set the same charset collation for identifier

CREATE tABLe users(id int,accounts varchar(100), identifier varchar(199)) CHARACTER SET 'utf8mb4' 
  COLLATE 'utf8mb4_unicode_520_ci'

SET @json='[ "license:4b5ef761bfcc3be1e86c3bf16e33f5432417ca6b", "license:4faca2e6f3fe74881e5fb91c9a02bbcfe1d3bb83" ]';

SELECT accounts
FROM users u
INNER JOIN
(
  SELECT * FROM json_table(@json, '$[*]'
    COLUMNS(
      identifier varchar(100) CHARACTER SET 'utf8mb4' 
  COLLATE 'utf8mb4_unicode_520_ci'  PATH '$' 
    )
  ) AS t1
) AS players
  ON u.identifier = players.identifier;

| accounts |
| :------- |

db<>fiddle here

Upvotes: 3

Related Questions