[FIXED] How to convert an object into a JSON_TABLE in MariaDB?

Issue

I have a products table which contains a JSON column product_logs. Inside of this, it contains something similar to:

{
  "c8eebc99-d936-3245-bc8d-17694f4ecb58": {
    "created_at": "2022-05-08T15:33:33.591166Z",
    "event": "product-created",
    "user": null
  },
  "ce7b171b-b479-332f-bf9e-54b948581179": {
    "created_at": "2022-05-08T15:33:33.591174Z",
    "event": "near-sell-by",
    "user": null
  }
}

I only want to return rows of products that have a near-sell-by event in the product_logs so I try to do this:

SELECT 
    products.*
FROM products,
     JSON_TABLE(product_logs, '$[*]', COLUMNS (
         created_at DATETIME PATH '$.created_at',
         event VARCHAR(MAX) PATH '$.event'
     ) logs
WHERE
    logs.event = 'near-sell-by'

However, I seem to be getting the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(product_logs, '$[*]', COLUMNS ( created_at DATETIME PATH '$.cr...' at line 4

Any help to where I'm going wrong would be greatly appreciated

Solution

You seem to have copied, from another database, there is no varchar8max) in mysql, to syntax is a bit complicated, and you need to undestand json pretty well.

a gui like workbench, at least can help you identify the error, but it will not help you

CREATE TABLE products (product_logs varchar(1209))

INSERT INTO products VALUES ('{
  "c8eebc99-d936-3245-bc8d-17694f4ecb58": {
    "created_at": "2022-05-08T15:33:33.591166Z",
    "event": "product-created",
    "user": null
  },
  "ce7b171b-b479-332f-bf9e-54b948581179": {
    "created_at": "2022-05-08T15:33:33.591174Z",
    "event": "near-sell-by",
    "user": null
  }
}
')

SELECT 
    products.*,logs.created_at,logs.event
FROM products,
     JSON_TABLE(products.product_logs, '$.*'
     COLUMNS (
         created_at DATETIME PATH '$.created_at',
         event Text PATH '$.event'
     )) logs
WHERE
    logs.event = 'near-sell-by'

product_logs                                                                                                                                                                                                                                                                                                                               | created_at          | event       
:----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------------------ | :-----------
{<br>  "c8eebc99-d936-3245-bc8d-17694f4ecb58": {<br>    "created_at": "2022-05-08T15:33:33.591166Z",<br>    "event": "product-created",<br>    "user": null<br>  },<br>  "ce7b171b-b479-332f-bf9e-54b948581179": {<br>    "created_at": "2022-05-08T15:33:33.591174Z",<br>    "event": "near-sell-by",<br>    "user": null<br>  }<br>}<br> | 2022-05-08 15:33:34 | near-sell-by

db<>fiddle here

Answered By - nbk

Answer Checked By - Katrina (PHPFixing Volunteer)