# 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)