[Bug]: Getting error if using json_extract in a MySQL query: Cannot invoke "Object.getClass()" because "columnValue" is null

[patrioticcow]

Is there an existing issue for this?

• I have searched the existing issues

Description

Getting error if using json_extract in a MySQL query, or just querying a field that is of json type.

SELECT
	id,
	createdAt as date,
	json_extract(details, '$.editor') AS editor
FROM
	`User`
ORDER BY
	id DESC
LIMIT 10

Steps To Reproduce

1. Run a MuSQL query on a table that has a json type field

Workaround

#27645 (comment)

Public Sample App

No response

Environment

Production

Issue video log

No response

Version

self host, V1.9.37.1

[subrata71]

@patrioticcow
Could it be due to the null value returned by json_extract(details, '$.editor')?

Can you please give the following queries a try?

Using IFNULL

SELECT
	id,
	createdAt as date,
	IFNULL(json_extract(details, '$.editor'), 'N/A') AS editor
FROM
	`User`
ORDER BY
	id DESC
LIMIT 10

Using COALESCE

SELECT
	id,
	createdAt as date,
	COALESCE(json_extract(details, '$.editor'), 'N/A') AS editor
FROM
	`User`
ORDER BY
	id DESC
LIMIT 10

[sumitsum]

As Subrata has mentioned seems like the issue occurs when the queried key is missing from the column JSON value returning null as the value against the key:

[sumitsum]

Hi @patrioticcow sharing a work around, please mention here if this helps. Re-writing the query like this should filter out all the null value results:

select json_extract(c_json, '$.key3') as result from test_json_type where json_type(json_extract(c_json, '$.key3')) != 'NULL';