CODEWITHSUNDEEP
sql-serversqlalchemy
Meitham
Meitham

Reputation: 9670

Stop SQLAlchemy from adding N prefix to literal string

I am using sqlalchemy core to build a query that includes json_extract/json_value function.

sa.func.json_value(table.c['json_field'], '$.attr')

SQLAlchemy is incorrectly adding the N prefix to the 'value' in the json_value

WHERE json_value(table.json_field, N'$.attr') = N'value'

This is raising the following error with SQLServer

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]JSON text is not properly formatted. Unexpected character 'n' is found at position 0. (13609) (SQLExecDirectW)")

How do I tell SQLAlchemy to not add N to that function argument? Like

  WHERE json_value(table.json_field, '$.attr') = N'value'

Upvotes: 0

Views: 182

Answers (1)

IMSoP
IMSoP

Reputation: 97648

The N in that query is not part of the string passed to json_value; it is part of the quoting syntax, used to designate a multibyte Unicode string encoding (the 'N' originally stood for "National", although the current manual rarely uses that terminology). I couldn't find a direct manual page about this syntax, but nchar and nvarchar are the related types.

The actual error is about the text inside the expression passed to json_value, i.e. the contents of the table.json_field column. The error message says that an unexpected 'n' was found at the beginning of one of those JSON values.

I'm going to take a guess that the string it's seeing is 'null', which may or may not be considered valid JSON on its own. The manual page for JSON_VALUE doesn't explicitly say what it expects, but the ISJSON function defaults to checking specifically for an object or array, suggesting that that is the common definition of "valid JSON" used in SQL Server's implementation.

Testing some simple SQL confirms that would give the error:

select json_value('null', '$');

Msg 13609 Level 16 State 1 Line 1 JSON text is not properly formatted. Unexpected character 'n' is found at position 0.

As compared to an empty object:

select json_value('{}', '$');

null

One fix might be to change the way the values are being stored to use an SQL NULL instead of a JSON 'null', which would then not give an error:

select json_value(null, '$');

null

Upvotes: 2

Related Questions