Add support for JSON datatype

[jlynchMicron]

Is your feature request related to a problem? Please describe.
Please add sqlalchemy_bigquery support for the JSON datatype, recently added to pre-GA support (link: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#json_type)

Describe alternatives you've considered
Ive considered just using the JSON data as a String and just doing JSON operations locally.

[cullenwren95]

Bumping this, would be great functionality to add.

[jlynchMicron]

Hi all, now that JSON is out of pre-GA and officially released (I think), could this ticket get bumped in priority for implementation?

[jlynchMicron]

Since bigquery-sqlalchemy is just an extension of sqlalchemy, does that mean there is already inbuilt support for this datatype due to it being an already existing sqlachemy datatype? https://docs.sqlalchemy.org/en/14/core/type_basics.html#sqlalchemy.types.JSON

[jlynchMicron]

Nevermind, looks like JSON is not in the sqlalchemy type map. https://github.com/googleapis/python-bigquery-sqlalchemy/blob/main/sqlalchemy_bigquery/_types.py#L32

[sachsbl]

+1 would be great to have this

[sar009]

@tswast I would like to contribute here. is there a wiki or a guide on how to get started?

[lambrosopos]

does anyone know what the current status is for this?

[deepakmali]

Is this being implemented? Has someone found alternative to this ?
I am querying for a json column and expecting to json value, but getting string instead.

[mephmanx]

I need this as well

[pykenny]

For anyone who want to take a stab on this topic:

There's very little consensus on JSON accessing syntax among various SQL implementations and it's up until 2023 did ISO add specification of JSON column type.

If you're only using SQLAlchemy for managing table schema and perhaps dump full JSON content from queries, then simply adding JSON to type map should probably be fine. However when it comes to internal value accessing, you'll need to instruct SA what to do with the query.

1. Internal value accessing (path querying)

   In Postgres, the implementation simply applies Postgres' arrow-like syntax with the key, and let Postgres itself to guess if it's accessing an array (integer key) or an object (string key), while in MySQL it has to identify key type to decide which syntax to use (square bracket subscription for arrays, and dot operator for objects). Also, some dialect-specific operators/syntax may exist (e.g. Postgres allows user to check if a JSONB contains some set of keys using ?&, with no equivalent functional call available) other than ones for accessing internal values.

2. Type conversion

   Unlike STRUCT, JSON doesn't contain any internal schema information, so type conversion should be done to escape JSON type first when comparing accessed value to others applied to binary operators (such as comparison). While user can call CAST explicitly, as far as I know both MySQL and Postgres dialects calls CAST internally depends on the type on the other side.

For BigQuery's case:

• Value/Path accessing: Using square bracket subscription for accessing both arrays and objects. (Using MySQL's syntax when using JSON_QUERY function to query value) and that's the only operator available for handling JSON type
• Type conversion: Available BigQuery column types for casting primitive values in JSON: STRING, BOOL, INT64, FLOAT64