CODEWITHSUNDEEP
mysql
ykay says Reinstate Monica
ykay says Reinstate Monica

Reputation: 4862

JSON_EXTRACT not working when not all rows contain JSON

I am trying to run JSON EXTRACT but get the following error:

Data truncation: Invalid JSON text in argument 1 to function json_extract: "The document is empty." at position 0.

select id, JSON_EXTRACT(content, "$.pathway_id") from reports

Upvotes: 25

Views: 20285

Answers (2)

aeron13
aeron13

Reputation: 325

I had a similar problem generated by Laravel method WhereJsonContains(): invalid json text in argument 1 to function json_contains: "the document is empty." at position 0.. The issue was due to the fact that some records in the database were EMPTY. I solved it by querying for not empty values, like this:

Model::where('record', '<>', '')
        ->WhereJsonContains('record', $value)
        ->get();

In SQL terms it would look like this:

SELECT id, JSON_EXTRACT(content, "$.pathway_id") FROM reports WHERE content<>'';

I know OP question is already solved, but this may help someone else

Upvotes: 0

ykay says Reinstate Monica
ykay says Reinstate Monica

Reputation: 4862

You can prevent this error by using JSON_VALID to make sure the field contains JSON as follows:

select id, CASE WHEN JSON_VALID(content) THEN JSON_EXTRACT(content, "$.pathway_id") ELSE null END from reports

Upvotes: 53

Related Questions