Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/dialects/mssql/json.py: 38%

1# dialects/mssql/json.py 

2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors 

3# <see AUTHORS file> 

4# 

5# This module is part of SQLAlchemy and is released under 

6# the MIT License: https://www.opensource.org/licenses/mit-license.php 

7from __future__ import annotations 

8 

9from typing import Any 

10from typing import TYPE_CHECKING 

11 

12from ... import types as sqltypes 

13from ...sql.sqltypes import _T_JSON 

14 

15if TYPE_CHECKING: 

16 from ...engine.interfaces import Dialect 

17 from ...sql.type_api import _BindProcessorType 

18 from ...sql.type_api import _LiteralProcessorType 

19 

20# technically, all the dialect-specific datatypes that don't have any special 

21# behaviors would be private with names like _MSJson. However, we haven't been 

22# doing this for mysql.JSON or sqlite.JSON which both have JSON / JSONIndexType 

23# / JSONPathType in their json.py files, so keep consistent with that 

24# sub-convention for now. A future change can update them all to be 

25# package-private at once. 

26 

27 

28class JSON(sqltypes.JSON[_T_JSON]): 

29 """MSSQL JSON type. 

30 

31 MSSQL supports JSON-formatted data as of SQL Server 2016. 

32 

33 The :class:`_mssql.JSON` datatype at the DDL level will represent the 

34 datatype as ``NVARCHAR(max)``, but provides for JSON-level comparison 

35 functions as well as Python coercion behavior. 

36 

37 :class:`_mssql.JSON` is used automatically whenever the base 

38 :class:`_types.JSON` datatype is used against a SQL Server backend. 

39 

40 .. seealso:: 

41 

42 :class:`_types.JSON` - main documentation for the generic 

43 cross-platform JSON datatype. 

44 

45 The :class:`_mssql.JSON` type supports persistence of JSON values 

46 as well as the core index operations provided by :class:`_types.JSON` 

47 datatype, by adapting the operations to render the ``JSON_VALUE`` 

48 or ``JSON_QUERY`` functions at the database level. 

49 

50 The SQL Server :class:`_mssql.JSON` type necessarily makes use of the 

51 ``JSON_QUERY`` and ``JSON_VALUE`` functions when querying for elements 

52 of a JSON object. These two functions have a major restriction in that 

53 they are **mutually exclusive** based on the type of object to be returned. 

54 The ``JSON_QUERY`` function **only** returns a JSON dictionary or list, 

55 but not an individual string, numeric, or boolean element; the 

56 ``JSON_VALUE`` function **only** returns an individual string, numeric, 

57 or boolean element. **both functions either return NULL or raise 

58 an error if they are not used against the correct expected value**. 

59 

60 To handle this awkward requirement, indexed access rules are as follows: 

61 

62 1. When extracting a sub element from a JSON that is itself a JSON 

63 dictionary or list, the :meth:`_types.JSON.Comparator.as_json` accessor 

64 should be used:: 

65 

66 stmt = select(data_table.c.data["some key"].as_json()).where( 

67 data_table.c.data["some key"].as_json() == {"sub": "structure"} 

68 ) 

69 

70 2. When extracting a sub element from a JSON that is a plain boolean, 

71 string, integer, or float, use the appropriate method among 

72 :meth:`_types.JSON.Comparator.as_boolean`, 

73 :meth:`_types.JSON.Comparator.as_string`, 

74 :meth:`_types.JSON.Comparator.as_integer`, 

75 :meth:`_types.JSON.Comparator.as_float`:: 

76 

77 stmt = select(data_table.c.data["some key"].as_string()).where( 

78 data_table.c.data["some key"].as_string() == "some string" 

79 ) 

80 

81 .. versionadded:: 1.4 

82 

83 

84 """ 

85 

86 # note there was a result processor here that was looking for "number", 

87 # but none of the tests seem to exercise it. 

88 

89 

90# Note: these objects currently match exactly those of MySQL, however since 

91# these are not generalizable to all JSON implementations, remain separately 

92# implemented for each dialect. 

93class _FormatTypeMixin: 

94 def _format_value(self, value: Any) -> str: 

95 raise NotImplementedError() 

96 

97 def bind_processor(self, dialect: Dialect) -> _BindProcessorType[Any]: 

98 super_proc = self.string_bind_processor(dialect) # type: ignore[attr-defined] # noqa: E501 

99 

100 def process(value: Any) -> Any: 

101 value = self._format_value(value) 

102 if super_proc: 

103 value = super_proc(value) 

104 return value 

105 

106 return process 

107 

108 def literal_processor( 

109 self, dialect: Dialect 

110 ) -> _LiteralProcessorType[Any]: 

111 super_proc = self.string_literal_processor(dialect) # type: ignore[attr-defined] # noqa: E501 

112 

113 def process(value: Any) -> str: 

114 value = self._format_value(value) 

115 if super_proc: 

116 value = super_proc(value) 

117 return value # type: ignore[no-any-return] 

118 

119 return process 

120 

121 

122class JSONIndexType(_FormatTypeMixin, sqltypes.JSON.JSONIndexType): 

123 def _format_value(self, value: Any) -> str: 

124 if isinstance(value, int): 

125 formatted_value = "$[%s]" % value 

126 else: 

127 formatted_value = '$."%s"' % value 

128 return formatted_value 

129 

130 

131class JSONPathType(_FormatTypeMixin, sqltypes.JSON.JSONPathType): 

132 def _format_value(self, value: Any) -> str: 

133 return "$%s" % ( 

134 "".join( 

135 [ 

136 "[%s]" % elem if isinstance(elem, int) else '."%s"' % elem 

137 for elem in value 

138 ] 

139 ) 

140 )