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.

Links

Repo

Commits and changes

Implemented operators and functions

| 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? |

Example data

      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"}}}');
  

Example queries

   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"}