bug: JSON schema union types that contain boolean result in casting JSON objects and arrays as boolean values

[edgarrmondragon]

Singer SDK Version

0.41.0

Is this a regression?

• Yes

Python Version

NA

Bug scope

Targets (data type handling, batching, SQL object generation, etc.)

Operating System

NA

Description

Fields with schemas similar to the one in the Code section below, prioritize the presence all types before object and array. This is wrong, as the presence of either indicates a non-scalar field.

The issue in question is coming from

sdk/singer_sdk/typing.py

Lines 1208 to 1245 in 316282e

	def to_sql_type( # noqa: PLR0911, C901
	jsonschema_type: dict,
	) -> sa.types.TypeEngine:
	"""Convert JSON Schema type to a SQL type.
	Args:
	jsonschema_type: The JSON Schema object.
	Returns:
	The SQL type.
	"""
	if _jsonschema_type_check(jsonschema_type, ("string",)):
	datelike_type = get_datelike_property_type(jsonschema_type)
	if datelike_type:
	if datelike_type == "date-time":
	return sa.types.DATETIME()
	if datelike_type in "time":
	return sa.types.TIME()
	if datelike_type == "date":
	return sa.types.DATE()
	maxlength = jsonschema_type.get("maxLength")
	return sa.types.VARCHAR(maxlength)
	if _jsonschema_type_check(jsonschema_type, ("integer",)):
	return sa.types.INTEGER()
	if _jsonschema_type_check(jsonschema_type, ("number",)):
	return sa.types.DECIMAL()
	if _jsonschema_type_check(jsonschema_type, ("boolean",)):
	return sa.types.BOOLEAN()
	if _jsonschema_type_check(jsonschema_type, ("object",)):
	return sa.types.VARCHAR()
	if _jsonschema_type_check(jsonschema_type, ("array",)):
	return sa.types.VARCHAR()
	return sa.types.VARCHAR()

Related issues:

• bug: Source postgres json & jsonb columns loading to snowflake as varchar true when using meltanolabs tap-postgres and target-snowflake MeltanoLabs/target-snowflake#274

Code

{
  "type": ["string","number","integer","array","object","boolean","null"]
}

Link to Slack/Linen

No response