Error Code: 1210. Incorrect arguments to JSON_TABLE

Question

I'm working with JSON tables and I'm running into an issue. I get the following error:

I'm using MySQL Engine version 8.0.20 on AWS with MySQL Workbench

When I use the JSON_Table command directly off of my table (sf.purchases), I am able to get the expected JSON output. For instance this works:

SELECT pur.*, jt.*
FROM sf.purchases AS pur,
JSON_TABLE (pur.items, '$[*]' 
    COLUMNS (
            C_jt_id FOR ORDINALITY,
            C_jt_item_uid VARCHAR(255) PATH '$.item_uid',
            C_jt_item_name VARCHAR(255) PATH '$.name',
            C_jt_qty INT PATH '$.qty',
            C_jt_price DOUBLE PATH '$.price')
        ) AS jt;

An it converts '[{"qty": "1", "name": "Test", "price": "59.99", "item_uid": "320-000002"}]'

into its respective columns.

However, when I create a VIEW I can't get JSON_TABLE to work on the resulting table

Here is the VIEW I create:

CREATE VIEW sf.lp AS
    (SELECT * FROM sf.purchases);

Here is the code that does NOT work

SELECT pur.*, jt.*
FROM sf.lp AS pur,
JSON_TABLE (pur.items, '$[*]' 
    COLUMNS (
            C_jt_id FOR ORDINALITY,
            C_jt_item_uid VARCHAR(255) PATH '$.item_uid',
            C_jt_item_name VARCHAR(255) PATH '$.name',
            C_jt_qty INT PATH '$.qty',
            C_jt_price DOUBLE PATH '$.price')
        ) AS jt;

The RESPONSE is: Error Code: 1210. Incorrect arguments to JSON_TABLE

I've checked the following:

I've reduced the COLUMNs to see if that affects anything
I've checked the dataset to see if any weird characters were somehow introduced
The two tables and the Columns and Datatypes are identical. Most importantly, items is of type JSON if both the TABLE (sf.purchases) and the VIEW (sf.lp)
Both the TABLE and VIEW use Character Set latin 1 and Collation latin1-swedish_ci
I've run JSON_STORAGE_SIZE to verify that items in the table adhere to the JSON format

SELECT pur.items,  
JSON_STORAGE_SIZE(pur.items)
FROM sf.lp pur

Any ideas or help would be appreciated.

user_5824668 · Answer

I think you were sad regarding no one replied your question, so, I decided to come back and help other people.

I have struggled with this problem and figured out why sometimes JSON_TABLE doesn't understand the source JSON.

In my case, I was sending another select result JSON field to CROSS JOIN JSON_TABLE and getting this error.

I thought the MySQL was not recognizing the resulting field as json, so, I used JSON_EXTRACT over the resulting field to "force" this recognition inside the JSON_TABLE statement.

It is like:

... CROSS JOIN JSON_TABLE(JSON_FIELD->'$[*]', COLUMNS ...)

I hope to help somebody :)

Error Code: 1210. Incorrect arguments to JSON_TABLE

1 Answers