0

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'
Meitham
  • 9,178
  • 5
  • 34
  • 45
  • 2
    I don't think this is the problem. It adds capital `N`, and error message mentions small `n`. There must be something wrong with the JSON. Have you tried the query in SSMS, does it work either way? – Roger Wolf Aug 02 '23 at 09:35
  • Do you understand the significance of the `N`, as in National character literals? The `N` isn't your problem, the error is complaining about the format of your input JSON. Something in your `table.json_field` values isn't valid JSON. What do you get if you `SELECT * FROM table WHERE json_field LIKE N'n%'`? – AlwaysLearning Aug 02 '23 at 09:45

1 Answers1

2

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

IMSoP
  • 89,526
  • 13
  • 117
  • 169
  • spot on, thank you, replacing these ``'null'`` strings with null fixed the issue, will see how these nulls crept in. – Meitham Aug 02 '23 at 11:58