MySQL Syntax Error when using JSON array as value

Question

My Table:

customer_id INT (PRIMARY KEY)
purchased_products JSON
refunded_products = JSON

Expected values

12345
["32","33","34"]
["31","38","39"]

The following SQL works as expected. Great!

 -- Insert a new row into the purchased products table
INSERT INTO dc_purchased_products (
    user_id, -- INT
    purchased_products -- JSON
)

-- 1) The user ID (primary key)
-- 2) Formatted json array with the first purchased product ID
VALUES ( 12345, '["36"]' )

-- If the user id already exists, append the existing array with the product ID
ON DUPLICATE KEY UPDATE 

-- JSON_ARRAY_INSERT(existing purchases array, index, product_id)
purchased_products = JSON_ARRAY_INSERT(purchased_products, '$[0]', "36")

However, my PDO satements in my application arent so good.

$item = [
  'statement' => "INSERT INTO purchased_products 
                        (customer_id, purchased_products) 
                  VALUES(:customer_id, [:purchased_products]) 
                    ON DUPLICATE KEY 
                    UPDATE purchased_products = JSON_ARRAY_INSERT(purchased_products, '$[0]',:purchased_products)",
  'data' => [
    ['customer_id' => 12345, 'purchased_products' => '"36"'],
    ['customer_id' => 12345, 'purchased_products' => '"37"']
  ]
]

My Connection

$this->connection = new PDO("mysql:host=$servername;dbname=$database", $u, $p, [
  PDO::MYSQL_ATTR_SSL_KEY                => $ck,
  PDO::MYSQL_ATTR_SSL_CERT               => $cc,
  PDO::MYSQL_ATTR_SSL_CA                 => $sc,
  PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false,
]);

$this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


$statement = $this->connection->prepare($item['statement']);
foreach ($item['data'] as $rowData) {

    foreach ($rowData as $key => $param) {
        $statement->bindValue(':' . $key, $param);
    }

    try {
        $success = $statement->execute();
    }

    catch (PDOException $e) {
        pre($e->getMessage());        
    }

}

Error message

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['3784835']) ON DUPLICATE KEY UPDATE purchased_products = JSON_ARRAY_INSERT(purc' at line 1

Your query has 3 parameters but you only pass 2, so you have to pass the value for purchased_products twice once if use in the INSERT and once for use in the UPDATE — RiggsFolly
– RiggsFolly, Commented Dec 17, 2021 at 15:46
But my query only has two params. customer_id and purchased_products. — Alex Knopp
– Alex Knopp, Commented Dec 17, 2021 at 15:57
Can you show us your connection parameter (not the password) but all the others — RiggsFolly
– RiggsFolly, Commented Dec 17, 2021 at 15:59
@RiggsFolly I had suspected this was something to do with the JSON syntax in the initial INSERT vs the UPDATE — Alex Knopp
– Alex Knopp, Commented Dec 17, 2021 at 16:25

masterguru · Accepted Answer · 2021-12-17 18:31:23Z

2

This should fix the problem:

$item = [
  'statement' => "INSERT INTO purchased_products 
                        (customer_id, purchased_products) 
                  VALUES(:customer_id, :purchased_products) 
                    ON DUPLICATE KEY 
                    UPDATE purchased_products = JSON_ARRAY_INSERT(purchased_products, '$[0]',:purchased_products_json)",
  'data' => [
    ['customer_id' => 12345, 'purchased_products' => '["36"]', 'purchased_products_json' => '36'],
    ['customer_id' => 12345, 'purchased_products' => '["37"]', 'purchased_products_json' => '37'],
  ]
];

Explanation of changes:

Removed [ and ] in VALUES(:customer_id, [:purchased_products])

Now it looks like this:

VALUES(:customer_id, :purchased_products)

This change will avoid the error during the execute().

New param in JSON_ARRAY_INSERT: purchased_products_json

Now it looks like this:

JSON_ARRAY_INSERT(purchased_products, '$[0]',:purchased_products_json)

To avoid the error now we need two params with small diferences. One for the INSERT (purchased_products) and other for the UPDATE (purchased_produtcs_json) because they have different formats despite same values.

In data array, changed 'purchased_products' => '"36"'

Now it looks like this:

'purchased_products' => '["36"]'

because we will use it during INSERT stament, and we need to set it as new JSON array value with correct format and because we already removed the [ and ] in the previous INSERT statement.

In data array, added new param: 'purchased_products_json'

In order to UPDATE the field, the value format should be different, so we need this new param. It looks like this:

'purchased_products_json' => '36'

avoiding the use of [ and ]. You can see some information about JSON_ARRAY_INSERT in mariadb documentation

edited Dec 17, 2021 at 18:31

answered Dec 17, 2021 at 17:09

masterguru

5774 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Knopp Over a year ago

that you so much for your efforts and I can see what your changes mean, it makes sense. Unfortunately I dont return to the office until Monday. However this will be the first thing I do and I will defo complete the question / answer etc.

masterguru Over a year ago

No hurry. Thanks. Be sure to verify it first because I am not able to reproduce it (well, I can, but too much work :-))

Alex Knopp Over a year ago

yup that nailed it. You were right to add the extra param for the update. Great work, thanks again.

Collectives™ on Stack Overflow

MySQL Syntax Error when using JSON array as value

1 Answer 1

Explanation of changes:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Explanation of changes:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related