📋

Description

This error occurs when the `JSON_TABLE` function attempts to extract a non-scalar JSON value (such as an array or an object) into a column that has been defined with a scalar SQL data type (e.g., INT, VARCHAR, DATE). MySQL expects a single, atomic JSON value for scalar columns, not complex structures.

💬

Error Message

Can't store an array or an object in the scalar JSON_TABLE column '%s'

🔍

Known Causes

3 known causes

⚠️

Mismatched Column Type Definition

The `JSON_TABLE` column is explicitly defined with a scalar SQL type, but the corresponding JSON path extracts an array or object.

⚠️

Incorrect JSON Path Specification

The JSON path used in `JSON_TABLE` mistakenly points to an array or object, when a scalar value was intended for the target SQL column.

⚠️

Unexpected JSON Data Structure

The input JSON data has an unforeseen structure, containing an array or object at a location where `JSON_TABLE` expected a scalar value for the defined column.

🛠️

Solutions

3 solutions available

1. Adjust JSON_TABLE Column Definition to Handle Complex Types medium

Modify the `COLUMNS` clause in `JSON_TABLE` to explicitly define columns for array elements or object properties.

1

Identify the problematic column in your `JSON_TABLE` definition that is attempting to store an array or object as a scalar value.

2

Modify the `COLUMNS` clause. If the JSON data contains arrays, define separate columns for each element or use an appropriate JSON function to extract specific elements. If it contains objects, define columns for each key-value pair.

sql
-- Original (problematic) definition for a column expecting a scalar:
-- COLUMNS (
--   scalar_column VARCHAR(255) PATH '$.some_array_or_object'
-- )

-- Solution for an array:
-- If $.some_array contains ["a", "b", "c"]
COLUMNS (
  first_element VARCHAR(255) PATH '$.some_array[0]',
  second_element VARCHAR(255) PATH '$.some_array[1]'
  -- or extract all into a single string:
  -- array_as_string TEXT PATH '$.some_array'
)

-- Solution for an object:
-- If $.some_object contains {"key1": "value1", "key2": "value2"}
COLUMNS (
  obj_key1 VARCHAR(255) PATH '$.some_object.key1',
  obj_key2 VARCHAR(255) PATH '$.some_object.key2'
)

3

Re-execute your `JSON_TABLE` query with the corrected column definitions.

2. Extract Scalar Values from JSON Arrays or Objects easy

Use JSON functions within `JSON_TABLE` to extract specific scalar values from arrays or object properties.

1

Examine the structure of your JSON data to understand where the arrays or objects reside.

2

In your `JSON_TABLE` definition, use JSON path expressions to target specific scalar values within those arrays or objects. For arrays, use index notation (e.g., `[0]`). For objects, use key notation (e.g., `.key_name`).

sql
-- Assuming JSON like: {"data": {"items": [{"id": 1, "name": "apple"}, {"id": 2, "name": "banana"}]}}
SELECT jt.* 
FROM your_table,
     JSON_TABLE(your_table.json_column, '$.data' COLUMNS (
       NESTED PATH '$.items[*]',
       item_id INT PATH '$.id',
       item_name VARCHAR(100) PATH '$.name'
     )) AS jt;

-- If you only wanted the first item's name:
SELECT jt.first_item_name
FROM your_table,
     JSON_TABLE(your_table.json_column, '$.data' COLUMNS (
       first_item_name VARCHAR(100) PATH '$.items[0].name'
     )) AS jt;

3

Ensure the data type of the extracted scalar value matches the column type you've defined in `JSON_TABLE`.

3. Store JSON Arrays/Objects as JSON Type in Target Columns medium

If your intention is to store the entire array or object, ensure the target column in your `SELECT` statement is of JSON type.

1

Identify the column in your `JSON_TABLE` definition that is intended to hold an array or object.

2

Change the data type of the corresponding column in your `SELECT` statement to `JSON`.

sql
-- Original (problematic) definition:
-- COLUMNS (
--   complex_data VARCHAR(255) PATH '$.some_array_or_object'
-- )

-- Corrected definition:
COLUMNS (
  complex_data JSON PATH '$.some_array_or_object'
)

3

Verify that the target table or the intermediate storage where you're placing these results can accommodate the `JSON` data type.

🔗

Related Errors

5 related errors

MySQL Error 3666: Invalid JSON_TABLE Scalar Value