I've released EctoPgJson, a new Elixir/Erlang library for Postgres JSON operators for JSON and JSONB datatypes. EctoPgJson provides a macro-based utility functions for Ecto queries.
| PG operator | right PG operand type | EctoPgJson function | description |
|---|---|---|---|
| -> | int | get_object(jsonb, field) | Get JSON array element |
| -> | text | get_object(jsonb, field) | Get JSON object field by key |
| ->> | int | get_text(jsonb, field) | Get JSON array element as text |
| ->> | text | get_text(jsonb, field) | Get JSON object field as text |
| #> | text[] | get_json_in(jsonb, field) | Get JSON object at specified path |
| #>> | text[] | get_text_in(jsonb, field) | Get JSON object at specified path as text |
| @> | jsonb | left_cotains?(jsonb, jsonb) | Does the left JSON value contain the right JSON path/value entries at the top level? |
| <@ | jsonb | right_contains?(jsonb, jsonb) | Are the left JSON path/value entries contained at the top level within the right JSON value? |
| ? | text | keys_exist?(jsonb, key) | Does the string exist as a top-level key within the JSON value? |
| ?| | text[] | keys_exist?(jsonb, any: strings) | Do any of these array strings exist as top-level keys? |
| ?& | text[] | keys_exist?(jsonb, all: strings) | Do all of these array strings exist as top-level keys? |
create table metrics (
id serial primary key,
attributes jsonb
);
insert into metrics (id, attributes)
values
(1, '{"data": "nope"}'),
(2, '{"data": {"level2": "yep"}}'),
(3, '{"data": {"level2": {"level3": "yepyep"}}}');
q =
from t in @table,
where: get_text(t.attributes, "data") == "nope",
select: t.id
assert TestRepo.one(q) == 1
q =
from t in @table,
where: t.id == 2,
select: get_object(t.attributes, "data")
assert TestRepo.one(q) == %{"level2" => "yep"}
q =
from t in @table,
where: t.id == 2,
select: get_text_in(t.attributes, "{data, level2}")
q2 =
from t in @table,
where: t.id == 2,
select: get_text_in(t.attributes, ["data", "level2"])
assert TestRepo.one(q) == TestRepo.one(q2)
assert TestRepo.one(q) == "yep"
q =
from t in @table,
where: t.id == 3,
select: get_object_in(t.attributes, "{data, level2}")
q2 =
from t in @table,
where: t.id == 3,
select: get_object_in(t.attributes, ["data", "level2"])
assert TestRepo.one(q) == TestRepo.one(q2)
assert TestRepo.one(q) == %{"level3" => "yepyep"}
(*｀□)<炎炎炎炎