How to convert an object into a JSON_TABLE in MariaDB?-CodePudding

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

CodePudding user response：

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