How to convert an object into a JSON_TABLE in MariaDB?

Question

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

I edited to remove references to MySQL, because you are not using MySQL. MariaDB is a different product. It originated as a fork from MySQL in 2010, but both products have been gradually changing since then. You should not assume MariaDB is compatible with MySQL anymore. — Bill Karwin
– Bill Karwin, Commented May 8, 2022 at 17:12
Thank you @BillKarwin - truthly, I thought xampp came with MySQL but it appears it is MariaDB! I appreciate the knowledge! — Jaquarh
– Jaquarh, Commented May 8, 2022 at 19:40

nbk · Accepted Answer · 2022-05-08 15:54:42Z

1

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 May 8, 2022 at 15:54

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

3 Comments

Jaquarh Over a year ago

Thank-you for the detailed explanation, I think I'm running MariaDB because I get this error message running this:

#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 '(products.product_logs, '$.*'

Perhaps I'm not running 10.6 and should update?

nbk Over a year ago

only 10.6 supports full json_table see dbfiddle.uk/… so update or switch to mysql

Jaquarh Over a year ago

Thanks @nbk I'm running Ver 15.1 Distrib 10.4.24-MariaDB I'll look to update this to 10.6 now. Thank-you very much for your time and explanation!

Collectives™ on Stack Overflow

How to convert an object into a JSON_TABLE in MariaDB?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related