JSON operators, v0.1.0 docs, PLOMID

Parsed at multiplicative level (expressions.rs); evaluated in compare.rs / ops.rs.

Op	Name	Returns	Example
`->`	arrow	JSON	`payload->'user'` → `{"name":"ada"}`
`->>`	arrow-text	text	`payload->>'name'` → `ada`
`#>`	path	JSON	`payload#>'{user,name}'`
`#>>`	path-text	text	`payload#>>'{user,name}'`
`#-`	delete-path	jsonb	`payload #- '{user,temp}'`
`arr[i]`	subscript	element	`payload['tags'][1]`, 1-based
`(r).f`	row field	value	`(addr).city`

sqlsource

SELECT payload->'user', payload->>'user', payload#>'{a,b}', payload#>>'{a,b}',
       payload #- '{tmp}', arr[1], (r).f FROM t;

Containment and existence#

Op	Function lowered to	Meaning
`@>`	`json_contains`	left contains right
`<@`	`contained_by`	left contained in right
`?`	`exists`	key exists
`?\|`	`exists_any`	any key exists
`?-inner`	`exists_all`	all keys exist
`@?`	`jsonb_path_exists_op`	path predicate matches
`@@`	`match_op`	path match

sqlsource

SELECT * FROM events WHERE payload @> '{"ok": true}';
SELECT * FROM events WHERE payload ? 'user';
SELECT * FROM events WHERE payload ?| ARRAY['a','b'];
SELECT * FROM events WHERE payload @? '$.user.name ? (@ starts with "a")';

Mutation operator#

sqlsource

SELECT payload - 'temp' FROM events;        -- drop key
SELECT payload - 1 FROM events;             -- drop array index
SELECT payload - ARRAY['a','b'] FROM events; -- drop keys
SELECT payload || '{"c": 1}' FROM events;   -- concat (objects merge, arrays concat, - sorts)

- lowers to jsonb_subtract; || to jsonb_concat.

NULL / missing#

Any navigation on missing field → NULL. Containment on NULL → NULL (row filtered). See NULL.

JSON operators

Navigation#

Containment and existence#

Mutation operator#

NULL / missing#

Continue