feat!: reading JSON data as a custom arrow extension type

[chelsea-lin]

We initially implemented a local pandas extension (db_dtypes.JSONType) for handling JSON data. Subsequently, the Arrow project introduced a native JSON data type in pyarrow after v19.0. We've opted to adopt this native type as our primary solution (see go/bf-json2 for internal design document). To ensure compatibility for users with older pyarrow versions, we've been using a custom Arrow extension as a fallback. This change transitions to using this custom Arrow extension as a stepping stone towards fully integrating the native pyarrow JSON type.

Release-As: 1.40.0

• Fixes internal issue 401054811
• Ensure the tests and linter pass
• Code coverage does not decrease (if any source code was changed)
• Appropriate docs were updated (if necessary)

Fixes internal issue 401054811🦕

[tswast]

Getting some test failures in presubmit:

FAILED tests/system/small/test_dataframe.py::test_df_drop_duplicates_w_json[first]
FAILED tests/system/small/test_dataframe.py::test_df_drop_duplicates_w_json[last]
FAILED tests/system/small/test_dataframe.py::test_df_drop_duplicates_w_json[False]
3 failed, 2715 passed, 16 skipped, 43 xfailed, 2 xpassed, 418 warnings in 1083.21s (0:18:03)

Also, could we make sure we add a Release-As: footer to our final commit message to make sure this doesn't trigger the 2.0 release? See: https://github.com/googleapis/release-please/blob/main/README.md#how-do-i-change-the-version-number

[tswast]

Thanks! One suggestion, but otherwise looks good.

[tswast]

Should we switch to pd.ArrowDtype(pyarrow.json_(pyarrow.string())) if pyarrow.json_ is available?

Also, would be good to make sure we align with OBJ_REF_DTYPE by creating a JSON_ARROW_TYPE variable to use here and there.

[chelsea-lin]

I will follow up with b/401055693. Thanks for reviewing!