Null field

Problem

You want to find documents where specific fields have Null values, even though Fauna does not store fields with Null values.

Solution

Create an index that includes a binding. The binding can reflect the state of a field even if that field was set to null and is not stored:

try
{
    Value result = await client.Query(
        CreateIndex(
            Obj(
                "name", "Letters_with_extra",
                "source", Obj(
                    "collection", Collection("Letters"),
                    "fields", Obj(
                        "has_extra", Query(
                            Lambda(
                                "document",
                                ContainsPath(
                                    Arr("data", "extra"),
                                    Var("document")
                                )
                            )
                        )
                    )
                ),
                "terms", Arr(
                    Obj("binding", "has_extra")
                ),
                "values", Arr(
                    Obj("field", Arr("data", "letter")),
                    Obj("field", Arr("ref"))
                )
            )
        )
    );
    Console.WriteLine(result);
}
catch (Exception e)
{
    Console.WriteLine($"ERROR: {e.Message}");
}
result, err := client.Query(
	f.CreateIndex(
		f.Obj{
			"name": "Letters_with_extra",
			"source": f.Obj{
				"collection": f.Collection("Letters"),
				"fields": f.Obj{
					"has_extra": f.Query(
						f.Lambda(
							"document",
							f.ContainsPath(
								f.Arr{"data", "extra"},
								f.Var("document"),
							),
						),
					),
				},
			},
			"terms": f.Arr{
				f.Obj{"binding": "has_extra"},
			},
			"values": f.Arr{
				f.Obj{"field": f.Arr{"data", "letter"}},
				f.Obj{"field": f.Arr{"ref"}},
			},
		},
	))

if err != nil {
	fmt.Fprintln(os.Stderr, err)
} else {
	fmt.Println(result)
}
System.out.println(
    client.query(
        CreateIndex(
            Obj(
                "name", Value("Letters_with_extra"),
                "source", Obj(
                    "collection", Collection("Letters"),
                    "fields", Obj(
                        "has_extra", Query(
                            Lambda(
                                "document",
                                ContainsPath(
                                    Arr(Value("data"), Value("extra")),
                                    Var("document")
                                )
                            )
                        )
                    )
                ),
                "terms", Arr(
                    Obj("binding", Value("has_extra"))
                ),
                "values", Arr(
                    Obj("field", Arr(Value("data"), Value("letter"))),
                    Obj("field", Arr(Value("ref")))
                )
            )
        )
    ).get());
client.query(
  q.CreateIndex({
    name: 'Letters_with_extra',
    source: {
      collection: q.Collection('Letters'),
      fields: {
        has_extra: q.Query(
          q.Lambda(
            'document',
            q.ContainsPath(
              ['data', 'extra'],
              q.Var('document')
            )
          )
        ),
      },
    },
    terms: [
      { binding: 'has_extra' },
    ],
    values: [
      { field: ['data', 'letter'] },
      { field: ['ref'] },
    ],
  })
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))
result = client.query(
  q.create_index({
    "name": "Letters_with_extra",
    "source": {
      "collection": q.collection("Letters"),
      "fields": {
        "has_extra": q.query(
          q.lambda_(
            "document",
            q.contains_path(
              ["data", "extra"],
              q.var("document")
            )
          )
        )
      }
    },
    "terms": [
      { "binding": "has_extra" }
    ],
    "values": [
      { "field": ["data", "letter"] },
      { "field": ["ref"] },
    ]
  })
)
print(result)
CreateIndex({
  name: 'Letters_with_extra',
  source: {
    collection: Collection('Letters'),
    fields: {
      has_extra: Query(
        Lambda(
          "document",
          ContainsPath(['data', 'extra'], Var("document"))
        )
      )
    },
  },
  terms: [
    { binding: 'has_extra' },
  ],
  values: [
    { field: ['data', 'letter'] },
    { field: ['ref'] },
  ],
})
Query metrics:

  •    bytesIn:   373

  •   bytesOut:   517

  • computeOps:     3

  •    readOps:     0

  •   writeOps:    27

  •  readBytes: 4,238

  • writeBytes: 4,371

  •  queryTime: 100ms

  •    retries:     0

This index is based on the Letters documents created in the Index tutorials.

With that index, it is easy to find the documents where the has_extra binding is false:

try
{
    Value result = await client.Query(
        Paginate(
            Match(Index("Letters_with_extra"), false)
        )
    );
    Console.WriteLine(result);
}
catch (Exception e)
{
    Console.WriteLine($"ERROR: {e.Message}");
}
ObjectV(data: Arr(Arr(StringV(M), RefV(id = "113", collection = RefV(id = "Letters", collection = RefV(id = "collections")))), Arr(StringV(Z), RefV(id = "126", collection = RefV(id = "Letters", collection = RefV(id = "collections"))))))
result, err := client.Query(
	f.Paginate(
		f.MatchTerm(f.Index("Letters_with_extra"), false),
	))

if err != nil {
	fmt.Fprintln(os.Stderr, err)
} else {
	fmt.Println(result)
}
map[data:[[M {113 0xc00007f9b0 0xc00007f9b0 <nil>}] [Z {126 0xc00007fb60 0xc00007fb60 <nil>}]]]
System.out.println(
    client.query(
        Paginate(
            Match(Index("Letters_with_extra"), Value(false))
        )
    ).get());
{data: [["M", ref(id = "113", collection = ref(id = "Letters", collection = ref(id = "collections")))], ["Z", ref(id = "126", collection = ref(id = "Letters", collection = ref(id = "collections")))]]}
client.query(
  q.Paginate(
    q.Match(q.Index('Letters_with_extra'), false)
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))
{
  data: [
    [ 'M', Ref(Collection("Letters"), "113") ],
    [ 'Z', Ref(Collection("Letters"), "126") ]
  ]
}
result = client.query(
  q.paginate(
    q.match(q.index("Letters_with_extra"), False)
  )
)
print(result)
{'data': [['M', Ref(id=113, collection=Ref(id=Letters, collection=Ref(id=collections)))], ['Z', Ref(id=126, collection=Ref(id=Letters, collection=Ref(id=collections)))]]}
Paginate(
  Match(Index("Letters_with_extra"), false)
)
{
  data: [
    [ 'M', Ref(Collection("Letters"), "113") ],
    [ 'Z', Ref(Collection("Letters"), "126") ]
  ]
}
Query metrics:

  •    bytesIn:   67

  •   bytesOut:  243

  • computeOps:    1

  •    readOps:    1

  •   writeOps:    0

  •  readBytes:  129

  • writeBytes:    0

  •  queryTime: 10ms

  •    retries:    0

Discussion

Fauna does not store fields with null values, which is a storage optimization to prevent needless storage costs. That means that for documents you create where a field value was set to null, it is as if the field was never specified.

When you use the Match function, the provided value(s) can never match fields that are not stored.

Index bindings execute when an index entry is created or updated, so that the computed value can be stored along with the index’s values fields. This means that the has_extra binding’s result accurately reflects whether the indexed document has an extra field.

Is this article helpful? 

Tell Fauna how the article can be improved:
 Visit Fauna's forums or email docs@fauna.com

Thank you for your feedback!