Claude
b83cc9988e
Co-Authored-By: Claude Sonnet 5.5 <[email protected]> Claude-Session: https://claude.ai/code/session_017ic4b6Kg9RWSTJzyz6KP9X
187 lines
10 KiB
Plaintext
187 lines
10 KiB
Plaintext
# MySQL 8.0.46-0ubuntu0.24.04.4
|
|
# Hibernate 7.4.14.Final
|
|
|
|
== CREATE TABLE as Hibernate emits it for MySQL
|
|
create table product (id bigint not null auto_increment, name varchar(255), attributes json, dimensions json, specs json, tags json, primary key (id)) engine=InnoDB;
|
|
|
|
== column types in information_schema
|
|
attributes json
|
|
dimensions json
|
|
specs json
|
|
tags json
|
|
|
|
== what MySQL stored (it normalises: keys sorted by length, then alphabetically)
|
|
attributes = {"color": "red", "price": 19.9, "stock": 12, "nested": {"aisles": [3, 7], "warehouse": "PUNE"}}
|
|
dimensions = {"depthMm": 180, "widthMm": 180, "heightMm": 360}
|
|
|
|
== dirty checking on MySQL
|
|
load, change nothing: 0 UPDATE; attributes.put("color","teal"): 1 UPDATE
|
|
stored color: teal
|
|
|
|
== HQL JSON functions on MySQL (same statements as 04-hql-json-functions-postgresql.txt)
|
|
|
|
== json_value, string
|
|
hql> select p.name, json_value(p.attributes, '$.color') from Product p order by p.id
|
|
sql> select p1_0.name,json_unquote(nullif(json_extract(p1_0.attributes,'$.color'),cast('null' as json))) from product p1_0 order by p1_0.id
|
|
OK (Desk lamp, teal); (Floor lamp, black); (Office chair, black); (Bookshelf, oak); (Monitor arm, silver)
|
|
|
|
== json_value, returning integer
|
|
hql> select p.name, json_value(p.attributes, '$.stock' returning integer) from Product p order by p.id
|
|
sql> select p1_0.name,cast(json_unquote(nullif(json_extract(p1_0.attributes,'$.stock'),cast('null' as json))) as signed) from product p1_0 order by p1_0.id
|
|
OK (Desk lamp, 12); (Floor lamp, 3); (Office chair, 0); (Bookshelf, 7); (Monitor arm, 25)
|
|
|
|
== json_value in where
|
|
hql> select p.name from Product p where json_value(p.attributes, '$.color') = 'black' order by p.id
|
|
sql> select p1_0.name from product p1_0 where json_unquote(nullif(json_extract(p1_0.attributes,'$.color'),cast('null' as json)))='black' order by p1_0.id
|
|
OK Floor lamp; Office chair
|
|
|
|
== json_value nested path + array index
|
|
hql> select p.name, json_value(p.attributes, '$.nested.aisles[1]' returning integer) from Product p order by p.id
|
|
sql> select p1_0.name,cast(json_unquote(nullif(json_extract(p1_0.attributes,'$.nested.aisles[1]'),cast('null' as json))) as signed) from product p1_0 order by p1_0.id
|
|
OK (Desk lamp, 7); (Floor lamp, 7); (Office chair, 7); (Bookshelf, 7); (Monitor arm, 7)
|
|
|
|
== json_value on a record column
|
|
hql> select p.name, json_value(p.dimensions, '$.widthMm' returning integer) from Product p order by p.id
|
|
sql> select p1_0.name,cast(json_unquote(nullif(json_extract(p1_0.dimensions,'$.widthMm'),cast('null' as json))) as signed) from product p1_0 order by p1_0.id
|
|
OK (Desk lamp, 180); (Floor lamp, 300); (Office chair, 600); (Bookshelf, 900); (Monitor arm, 150)
|
|
|
|
== json_value on a list column
|
|
hql> select p.name, json_value(p.tags, '$[0]') from Product p order by p.id
|
|
sql> select p1_0.name,json_unquote(nullif(json_extract(p1_0.tags,'$[0]'),cast('null' as json))) from product p1_0 order by p1_0.id
|
|
OK (Desk lamp, lighting); (Floor lamp, lighting); (Office chair, office); (Bookshelf, furniture); (Monitor arm, office)
|
|
|
|
== json_value, missing key
|
|
hql> select p.name, json_value(p.attributes, '$.nope') from Product p where p.id = 1
|
|
sql> select p1_0.name,json_unquote(nullif(json_extract(p1_0.attributes,'$.nope'),cast('null' as json))) from product p1_0 where p1_0.id=1
|
|
OK (Desk lamp, null)
|
|
|
|
== json_query (sub-document)
|
|
hql> select p.name, json_query(p.attributes, '$.nested') from Product p where p.id = 1
|
|
sql> select p1_0.name,nullif(json_extract(p1_0.attributes,'$.nested'),cast('null' as json)) from product p1_0 where p1_0.id=1
|
|
OK (Desk lamp, {"aisles": [3, 7], "warehouse": "PUNE"})
|
|
|
|
== json_query with wrapper
|
|
hql> select p.name, json_query(p.attributes, '$.nested.aisles[*]' with wrapper) from Product p where p.id = 1
|
|
sql> select p1_0.name,nullif(json_extract(p1_0.attributes,'$.nested.aisles[*]'),cast('null' as json)) from product p1_0 where p1_0.id=1
|
|
OK (Desk lamp, [3, 7])
|
|
|
|
== json_exists
|
|
hql> select p.name from Product p where json_exists(p.attributes, '$.nested.warehouse') order by p.id
|
|
sql> select p1_0.name from product p1_0 where json_contains_path(p1_0.attributes,'one','$.nested.warehouse') order by p1_0.id
|
|
OK Desk lamp; Floor lamp; Office chair; Bookshelf; Monitor arm
|
|
|
|
== json_exists with filter
|
|
hql> select p.name from Product p where json_exists(p.attributes, '$.stock ? (@ > 5)') order by p.id
|
|
sql> select p1_0.name from product p1_0 where json_contains_path(p1_0.attributes,'one','$.stock ? (@ > 5)') order by p1_0.id
|
|
FAILED SQLGrammarException: Invalid JSON path expression. The error is around character position 8.
|
|
|
|
== json_object
|
|
hql> select json_object('name', p.name, 'id', p.id) from Product p where p.id = 1
|
|
sql> select json_object('name',p1_0.name,'id',p1_0.id) from product p1_0 where p1_0.id=1
|
|
OK {"id": 1, "name": "Desk lamp"}
|
|
|
|
== json_object, key/value keywords
|
|
hql> select json_object(key 'name' value p.name) from Product p where p.id = 1
|
|
sql> select json_object('name',p1_0.name) from product p1_0 where p1_0.id=1
|
|
OK {"name": "Desk lamp"}
|
|
|
|
== json_array
|
|
hql> select json_array(p.name, p.id, null) from Product p where p.id = 1
|
|
sql> select json_array(p1_0.name,p1_0.id,null) from product p1_0 where p1_0.id=1
|
|
OK ["Desk lamp", 1, null]
|
|
|
|
== json_arrayagg
|
|
hql> select json_arrayagg(p.name) from Product p
|
|
sql> select cast(concat('[',group_concat(nullif(json_extract(json_array(p1_0.name),'$[0]'),cast('null' as json)) separator ','),']') as json) from product p1_0
|
|
OK ["Desk lamp", "Floor lamp", "Office chair", "Bookshelf", "Monitor arm"]
|
|
|
|
== json_objectagg
|
|
hql> select json_objectagg(key p.name value p.id) from Product p
|
|
sql> select concat('{',group_concat(concat(json_quote(p1_0.name),':',nullif(json_extract(json_array(p1_0.id),'$[0]'),cast('null' as json))) separator ','),'}') from product p1_0
|
|
OK {"Desk lamp":1,"Floor lamp":2,"Office chair":3,"Bookshelf":4,"Monitor arm":5}
|
|
|
|
== json_table over a list column
|
|
hql> select p.name, t.tag from Product p join lateral json_table(p.tags, '$[*]' columns (tag String path '$')) t where p.id = 1
|
|
sql> select p1_0.name,t1_0.tag from product p1_0 join json_table(p1_0.tags,'$[*]' columns(tag longtext path '$')) t1_0 on true where p1_0.id=1
|
|
OK (Desk lamp, lighting); (Desk lamp, office)
|
|
|
|
== json_table, tags counted
|
|
hql> select t.tag, count(*) from Product p join lateral json_table(p.tags, '$[*]' columns (tag String path '$')) t group by t.tag order by t.tag
|
|
sql> select t1_0.tag,count(*) from product p1_0 join json_table(p1_0.tags,'$[*]' columns(tag longtext path '$')) t1_0 on true group by t1_0.tag order by t1_0.tag
|
|
OK (accessories, 1); (furniture, 2); (home, 2); (lighting, 2); (office, 3)
|
|
|
|
== json_set
|
|
hql> update Product p set p.attributes = json_set(p.attributes, '$.color', 'green') where p.id = 1
|
|
sql> update product p1_0 set attributes=json_set(p1_0.attributes,'$.color','green') where p1_0.id=1
|
|
OK, 1 row(s) updated
|
|
|
|
== json_insert
|
|
hql> update Product p set p.attributes = json_insert(p.attributes, '$.brand', 'Acme') where p.id = 1
|
|
sql> update product p1_0 set attributes=json_insert(p1_0.attributes,'$.brand','Acme') where p1_0.id=1
|
|
OK, 1 row(s) updated
|
|
|
|
== json_replace
|
|
hql> update Product p set p.attributes = json_replace(p.attributes, '$.stock', 99) where p.id = 1
|
|
sql> update product p1_0 set attributes=json_replace(p1_0.attributes,'$.stock',99) where p1_0.id=1
|
|
OK, 1 row(s) updated
|
|
|
|
== json_remove
|
|
hql> update Product p set p.attributes = json_remove(p.attributes, '$.price') where p.id = 1
|
|
sql> update product p1_0 set attributes=json_remove(p1_0.attributes,'$.price') where p1_0.id=1
|
|
OK, 1 row(s) updated
|
|
|
|
== json_mergepatch
|
|
hql> update Product p set p.attributes = json_mergepatch(p.attributes, '{"stock": 1, "brand": null}') where p.id = 2
|
|
sql> update product p1_0 set attributes=json_merge_patch(p1_0.attributes,'{"stock": 1, "brand": null}') where p1_0.id=2
|
|
OK, 1 row(s) updated
|
|
|
|
== json_array_append, root path $
|
|
hql> update Product p set p.tags = json_array_append(p.tags, '$', 'sale') where p.id = 1
|
|
sql> update product p1_0 set tags=json_array_append(p1_0.tags,'$','sale') where p1_0.id=1
|
|
OK, 1 row(s) updated
|
|
|
|
== json_array_append, nested path
|
|
hql> update Product p set p.attributes = json_array_append(p.attributes, '$.nested.aisles', 11) where p.id = 1
|
|
sql> update product p1_0 set attributes=json_array_append(p1_0.attributes,'$.nested.aisles',11) where p1_0.id=1
|
|
OK, 1 row(s) updated
|
|
|
|
== json_array_insert
|
|
hql> update Product p set p.tags = json_array_insert(p.tags, '$[0]', 'first') where p.id = 2
|
|
sql> update product p1_0 set tags=json_array_insert(p1_0.tags,'$[0]','first') where p1_0.id=2
|
|
OK, 1 row(s) updated
|
|
|
|
== attribute navigation p.attributes.color
|
|
hql> select p.name from Product p where p.attributes.color = 'black'
|
|
FAILED IllegalArgumentException: Could not interpret attribute 'color' of basic-valued path 'com.ankurm.jsoncolumns.model.Product(p).attributes'
|
|
|
|
== native JSON functions and operators (MySQL spellings)
|
|
->> text extraction -> Floor lamp; Office chair
|
|
JSON_CONTAINS(tags, '"office"') -> Desk lamp; Office chair; Monitor arm
|
|
'office' MEMBER OF (tags) -> Desk lamp; Office chair; Monitor arm
|
|
JSON_OVERLAPS(tags, '["home","accessories"]') -> Floor lamp; Bookshelf; Monitor arm
|
|
JSON_CONTAINS_PATH(attributes, 'one', '$.nested.warehouse') -> 5
|
|
|
|
== @Embeddable as JSON on MySQL
|
|
sql> select c1_0.name from customer c1_0 where cast(json_unquote(nullif(json_extract(c1_0.address,'$.city'),cast('null' as json))) as char)='Pune'
|
|
result: [Asha]
|
|
|
|
== indexing 50,000 rows on MySQL
|
|
rows: 50000
|
|
|
|
== no index
|
|
sql> select count(*) from event where payload->>'$.userId' = '42'
|
|
type=ALL key=null rows=49800 extra=Using where
|
|
sql> select count(*) from event where cast(payload->>'$.userId' as unsigned) = 42
|
|
type=ALL key=null rows=49800 extra=Using where
|
|
sql> select count(*) from event where 'vip' member of (payload->'$.tags')
|
|
type=ALL key=null rows=49800 extra=Using where
|
|
|
|
== functional index on cast(payload->>'$.userId' as unsigned), multi-valued index on tags
|
|
sql> select count(*) from event where payload->>'$.userId' = '42'
|
|
type=ALL key=null rows=49800 extra=Using where
|
|
sql> select count(*) from event where cast(payload->>'$.userId' as unsigned) = 42
|
|
type=ref key=idx_user rows=10 extra=null
|
|
sql> select count(*) from event where 'vip' member of (payload->'$.tags')
|
|
type=ref key=idx_tags rows=1000 extra=Using where
|
|
count via index: 10 / vip rows: 1000
|