5

I have an OPENJSON command that takes the parsed JSON and LEFT joins it onto an existing table.

When I add the LEFT JOIN I get the error:

collation conflict between "Latin1_General_BIN2" and "SQL_Latin1_General_CP1_CI_AS"

The table has the same collation for all string columns as: SQL_Latin1_General_CP1_CI_AS

I've tried adding COLLATE DATABASE_DEFAULT in the LEFT JOIN, but with no improvement.

The query I'm using is roughly as:

DECLARE @json NVARCHAR(MAX) = '
{
    "ExampleJson": {
        "stuff": [
            {
                "_program_id": "hello",
                "work_date": "2021-03-23 00:00:00"
            }
        ]
    }
}';

SELECT *
FROM 
OPENJSON
(
    (
        @json
    ), '$.ExampleJson.stuff'
) 
 
WITH ( 
     [program_id] NVARCHAR(255) '$."program_id"'
     ,[work_date] DATETIME '$."work_date"'
) [json_data] 
  
 
LEFT JOIN 
    [existing_db_data]
    ON [existing_db_data].[program_id] = [json_data].[program_id]
Slicc
  • 3,217
  • 7
  • 35
  • 70

1 Answers1

6

Interesting problem which I cannot reproduce but you should be able to resolve by placing the correct collation in either the WITH clause or after the join, eg

WITH ( 
     [program_id] NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CI_AS '$."_program_id"' 
     ,[work_date] DATETIME '$."work_date"'
) [json_data] 
    LEFT JOIN [existing_db_data] d ON d.[program_id] = [json_data].[program_id] COLLATE SQL_Latin1_General_CP1_CI_AS

Either should suffice, you do not need both.

wBob
  • 13,710
  • 3
  • 20
  • 37
  • Thanks for the suggestion, neither seems to work unfortunately. – Slicc Oct 06 '21 at 13:53
  • Ahh, if I do it the other way round I get better results, i.e. Latin1_General_BIN2 on the existing_db_data column – Slicc Oct 06 '21 at 13:56
  • Interesting. What is the collation of your dedicated SQL pool? – wBob Oct 06 '21 at 13:57
  • Same as the columns: SQL_Latin1_General_CP1_CI_AS – Slicc Oct 06 '21 at 13:58
  • You might have run the version of my cod which accidentally had a case-sensitive (CS) collation in the inner section. This reproduced the problem and I was able to fix with the outer `COLLATE`. – wBob Oct 06 '21 at 13:59
  • 3
    I tried again with SQL_Latin1_General_CP1_CI_AS, but still no luck. BIN2 collation of the left table is fine though, so will go with that thanks. – Slicc Oct 06 '21 at 14:01