Claude
b83cc9988e
Co-Authored-By: Claude Sonnet 5.5 <[email protected]> Claude-Session: https://claude.ai/code/session_017ic4b6Kg9RWSTJzyz6KP9X
95 lines
4.0 KiB
Plaintext
95 lines
4.0 KiB
Plaintext
# PostgreSQL 18.6 on x86_64-pc-linux-gnu
|
|
|
|
== ->> text extraction
|
|
sql> select name from product where attributes->>'color' = 'black' order by id
|
|
OK Floor lamp; Office chair
|
|
|
|
== ->> with a named parameter
|
|
sql> select name from product where attributes->>'color' = :c order by id -- {c=black}
|
|
OK Floor lamp; Office chair
|
|
|
|
== -> then #>> path
|
|
sql> select name from product where attributes #>> '{nested,warehouse}' = 'PUNE' and id = 1
|
|
OK Desk lamp
|
|
|
|
== ->> cast to int for comparison
|
|
sql> select name from product where (attributes->>'stock')::int > 5 order by id
|
|
OK Desk lamp; Bookshelf; Monitor arm
|
|
|
|
== @> containment, literal
|
|
sql> select name from product where tags @> '["office"]' order by id
|
|
OK Desk lamp; Office chair; Monitor arm
|
|
|
|
== @> containment, parameter with cast(:p as jsonb)
|
|
sql> select name from product where attributes @> cast(:p as jsonb) order by id -- {p={"color":"black"}}
|
|
OK Floor lamp; Office chair
|
|
|
|
== @> containment, parameter with :p::jsonb
|
|
sql> select name from product where attributes @> :p::jsonb order by id -- {p={"color":"black"}}
|
|
FAILED IllegalArgumentException: No parameter named ':p' in query with named parameters [p::jsonb]
|
|
|
|
== ? key-exists, bare question mark
|
|
sql> select name from product where attributes ? 'color' order by id
|
|
FAILED QueryParameterException: No argument for ordinal parameter '?1'
|
|
|
|
== ? key-exists, backslash-escaped
|
|
sql> select name from product where attributes \? 'color' order by id
|
|
FAILED DataException: No value specified for parameter 1.
|
|
|
|
== ? key-exists, each ? escaped: \?\?
|
|
sql> select name from product where attributes \?\? 'color' order by id
|
|
OK Desk lamp; Floor lamp; Office chair; Bookshelf; Monitor arm
|
|
|
|
== ? key-exists, doubled ??
|
|
sql> select name from product where attributes ?? 'color' order by id
|
|
FAILED QueryParameterException: No argument for ordinal parameter '?1'
|
|
|
|
== jsonb_exists() function instead of ?
|
|
sql> select name from product where jsonb_exists(attributes, 'color') order by id
|
|
OK Desk lamp; Floor lamp; Office chair; Bookshelf; Monitor arm
|
|
|
|
== ?| any-key, doubled ??|
|
|
sql> select name from product where tags ??| array['home','accessories'] order by id
|
|
FAILED QueryParameterException: No argument for ordinal parameter '?1'
|
|
|
|
== ?| any-key, escaped: \?\?|
|
|
sql> select name from product where tags \?\?| array['home','accessories'] order by id
|
|
OK Floor lamp; Bookshelf; Monitor arm
|
|
|
|
== jsonb_exists_any() function instead of ?|
|
|
sql> select name from product where jsonb_exists_any(tags, array['home','accessories']) order by id
|
|
OK Floor lamp; Bookshelf; Monitor arm
|
|
|
|
== jsonb_path_exists()
|
|
sql> select name from product where jsonb_path_exists(attributes, '$.stock ? (@ > 5)') order by id
|
|
OK Desk lamp; Bookshelf; Monitor arm
|
|
|
|
== @? path operator
|
|
sql> select name from product where attributes @? '$.stock ? (@ > 5)' order by id
|
|
FAILED QueryParameterException: No argument for ordinal parameter '?1'
|
|
|
|
== @? path operator, escaped: @\?\?
|
|
sql> select name from product where attributes @\?\? '$.stock ? (@ > 5)' order by id
|
|
OK Desk lamp; Bookshelf; Monitor arm
|
|
|
|
== @@ path predicate
|
|
sql> select name from product where attributes @@ '$.stock > 5' order by id
|
|
OK Desk lamp; Bookshelf; Monitor arm
|
|
|
|
== jsonb_array_elements_text in a lateral join
|
|
sql> select t.tag, count(*) from product p, jsonb_array_elements_text(p.tags) t(tag) group by t.tag order by t.tag
|
|
OK (accessories, 1); (furniture, 2); (home, 2); (lighting, 2); (office, 3)
|
|
|
|
== jsonb_set in an UPDATE (native)
|
|
sql> update product set attributes = jsonb_set(attributes, '{color}', to_jsonb(cast(:v as text))) where id = 3 -- {v=white}
|
|
OK, 1 row(s) updated
|
|
|
|
== What does a native SELECT of a jsonb column hand back to Java?
|
|
createNativeQuery(sql).getSingleResult() -> java.lang.String
|
|
createNativeQuery(sql, Map.class) -> java.util.HashMap
|
|
createNativeQuery(sql, Product.class) -> attributes.color = red
|
|
|
|
== HQL equality on a jsonb column with a Java value as parameter
|
|
where p.tags = :t with List.of("furniture","home") -> [Bookshelf]
|
|
where p.tags = :t with List.of("home","furniture") -> [] (jsonb arrays are ordered)
|