qjson (equery v0.22.0)
View SourceJSON / JSONB operators, builders, and mutation.
Three families:
- Access operators —
->,->>,#>,#>>(PG's JSON path operators). - Predicates —
@>,<@,?,?|,?&(containment and key existence; same operator atoms as their array siblings inpg_sql). - Builders & mutation —
jsonb_build_object/1(takes an Erlang map),jsonb_build_array/1,to_jsonb/1,row_to_json/1,array_to_json/1,jsonb_set/3,4,jsonb_insert/3,4,jsonb_strip_nulls/1.
q:select(fun([#{id := Id, name := N}]) ->
#{obj => qjson:jsonb_build_object(#{
id => Id,
name => N,
flag => Id > 10
})}
end).Summary
Access
json #> path — extract by path (list of keys/indexes), as json/jsonb.
json #>> path — extract by path, as text.
json -> key — get JSON field as json/jsonb. See JSON Operators.
json ->> key — get JSON field as text.
Builders
array_to_json(arr) — convert an SQL array to a JSON array.
json_build_array(v1, v2, ...) — build a JSON array from a list of values.
json_build_object(k1, v1, k2, v2, ...) from an Erlang map.
jsonb_build_array(...).
jsonb_build_object(...) — binary JSON variant of json_build_object/1.
row_to_json(record) — convert a row/record to a JSON object with column names as keys.
to_json(value) — convert any SQL value to json. Records become objects, arrays become JSON arrays.
to_jsonb(value) — same as to_json, returns jsonb.
Mutation
jsonb_insert(target, path, new_value) — insert before the position pointed to by path.
jsonb_insert(target, path, new_value, insert_after) — when insert_after is true, insert after the path.
jsonb_set(target, path, new_value) — set value at path. Path is an
Erlang list of binary keys (auto-converted to text[]).
jsonb_set(target, path, new_value, create_missing) — when create_missing is true, creates the path if it does not exist.
jsonb_strip_nulls(target) — recursively remove object fields with null values.
Predicates
jsonb ?& keys — does it contain all of the given keys?
jsonb ? key — does the object/array contain the key (or text element)?
jsonb ?| keys — does it contain any of the given keys?
jsonb <@ obj — is contained by.
jsonb @> obj — contains. See JSONB Containment.
Access
json #> path — extract by path (list of keys/indexes), as json/jsonb.
json #>> path — extract by path, as text.
json -> key — get JSON field as json/jsonb. See JSON Operators.
json ->> key — get JSON field as text.
Builders
-spec array_to_json(qast:ast_node()) -> qast:ast_node().
array_to_json(arr) — convert an SQL array to a JSON array.
-spec json_build_array(list()) -> qast:ast_node().
json_build_array(v1, v2, ...) — build a JSON array from a list of values.
-spec json_build_object(map()) -> qast:ast_node().
json_build_object(k1, v1, k2, v2, ...) from an Erlang map.
Keys (atoms or binaries) become text literals; values are arbitrary AST. See Builder Functions.
qjson:json_build_object(#{
id => Id,
name => Name,
flag => Id > 10
}).-spec jsonb_build_array(list()) -> qast:ast_node().
jsonb_build_array(...).
-spec jsonb_build_object(map()) -> qast:ast_node().
jsonb_build_object(...) — binary JSON variant of json_build_object/1.
-spec row_to_json(qast:ast_node()) -> qast:ast_node().
row_to_json(record) — convert a row/record to a JSON object with column names as keys.
-spec to_json(qast:ast_node() | any()) -> qast:ast_node().
to_json(value) — convert any SQL value to json. Records become objects, arrays become JSON arrays.
-spec to_jsonb(qast:ast_node() | any()) -> qast:ast_node().
to_jsonb(value) — same as to_json, returns jsonb.
Mutation
-spec jsonb_insert(qast:ast_node(), [binary()], qast:ast_node() | any()) -> qast:ast_node().
jsonb_insert(target, path, new_value) — insert before the position pointed to by path.
-spec jsonb_insert(qast:ast_node(), [binary()], qast:ast_node() | any(), boolean()) -> qast:ast_node().
jsonb_insert(target, path, new_value, insert_after) — when insert_after is true, insert after the path.
-spec jsonb_set(qast:ast_node(), [binary()], qast:ast_node() | any()) -> qast:ast_node().
jsonb_set(target, path, new_value) — set value at path. Path is an
Erlang list of binary keys (auto-converted to text[]).
qjson:jsonb_set(Data, [<<"prefs">>, <<"theme">>], NewTheme).-spec jsonb_set(qast:ast_node(), [binary()], qast:ast_node() | any(), boolean()) -> qast:ast_node().
jsonb_set(target, path, new_value, create_missing) — when create_missing is true, creates the path if it does not exist.
-spec jsonb_strip_nulls(qast:ast_node()) -> qast:ast_node().
jsonb_strip_nulls(target) — recursively remove object fields with null values.
Predicates
jsonb ?& keys — does it contain all of the given keys?
jsonb ? key — does the object/array contain the key (or text element)?
jsonb ?| keys — does it contain any of the given keys?
jsonb <@ obj — is contained by.
jsonb @> obj — contains. See JSONB Containment.