Join

Join( source, target )
Join( source, target )
Join( source, target )
Join( source, target )
join( source, target )
Join( source, target )
Join( source, target )

Description

The Join function joins the source set’s tuples with the target. target can be one of:

  • An index Reference.

    The source set’s tuples are iterated, and an implicit Match call is made for each tuple against the target index’s terms. The union of the results is returned.

  • A Lambda function.

    The source set’s tuples are iterated and passed to the function. The function decides how each tuple should be handled. The union of the results is returned.

    The Lambda function must be pure, i.e. it may not make any reads or writes.

In either case, if the source set’s tuples do not match the number and types of the target, an error occurs.

Parameters

Argument Type Definition and Requirements

source

Set Reference

The source Set Reference for the join operation.

target

An index Reference or Lambda function

The index Reference to join with the source Set Reference, or the Lambda function which determines how to complete the join operation.

Returns

The Set Reference for the union of the results from the join operation.

Examples

  1. The following query joins the "spellbooks" documents for a specific owner with the "spells_by_spellbook" index, which reports the spells for each spellbook, answering the question "what spells can this owner cast?":

    try
{
    Value result = await client.Query(
        Map(
            Paginate(
                Join(
                    Match(
                        Index("spellbooks_by_owner"),
                        Ref(Collection("characters"), "181388642114077184")
                    ),
                    Index("spells_by_spellbook")
                )
            ),
            Lambda("ref", Get(Var("ref")))
        )
    );
    Console.WriteLine(result);
}
catch (Exception e)
{
    Console.WriteLine($"ERROR: {e.Message}");
}
    ObjectV(data: Arr(ObjectV(ref: RefV(id = "181388642046968320", collection = RefV(id = "spells", collection = RefV(id = "collections"))),ts: LongV(1626225336060000),data: ObjectV(name: StringV(Fire Beak),element: Arr(StringV(air), StringV(fire)),spellbook: RefV(id = "181388642139243008", collection = RefV(id = "spellbooks", collection = RefV(id = "collections"))))), ObjectV(ref: RefV(id = "181388642071085568", collection = RefV(id = "spells", collection = RefV(id = "collections"))),ts: LongV(1626225336060000),data: ObjectV(name: StringV(Water Dragon's Claw),element: Arr(StringV(water), StringV(fire)),spellbook: RefV(id = "181388642139243008", collection = RefV(id = "spellbooks", collection = RefV(id = "collections")))))))
    result, err := client.Query(
	f.Map(
		f.Paginate(
			f.Join(
				f.MatchTerm(
					f.Index("spellbooks_by_owner"),
					f.Ref(f.Collection("characters"), "181388642114077184")),
				f.Index("spells_by_spellbook"),
			),
		),
		f.Lambda("ref", f.Get(f.Var("ref"))),
	))

if err != nil {
	fmt.Fprintln(os.Stderr, err)
} else {
	fmt.Println(result)
}
    map[data:[map[data:map[element:[air fire] name:Fire Beak spellbook:{181388642139243008 0xc000092780 0xc000092780 <nil>}] ref:{181388642046968320 0xc0000925a0 0xc0000925a0 <nil>} ts:1626225336060000] map[data:map[element:[water fire] name:Water Dragon's Claw spellbook:{181388642139243008 0xc000092b40 0xc000092b40 <nil>}] ref:{181388642071085568 0xc000092960 0xc000092960 <nil>} ts:1626225336060000]]]
    System.out.println(
    client.query(
        Map(
            Paginate(
                Join(
                    Match(
                        Index("spellbooks_by_owner"),
                        Ref(Collection("characters"), "181388642114077184")
                    ),
                    Index("spells_by_spellbook")
                )
            ),
            Lambda("ref", Get(Var("ref")))
        )
    ).get());
    {data: [{ref: ref(id = "181388642046968320", collection = ref(id = "spells", collection = ref(id = "collections"))), ts: 1626225336060000, data: {name: "Fire Beak", element: ["air", "fire"], spellbook: ref(id = "181388642139243008", collection = ref(id = "spellbooks", collection = ref(id = "collections")))}}, {ref: ref(id = "181388642071085568", collection = ref(id = "spells", collection = ref(id = "collections"))), ts: 1626225336060000, data: {name: "Water Dragon's Claw", element: ["water", "fire"], spellbook: ref(id = "181388642139243008", collection = ref(id = "spellbooks", collection = ref(id = "collections")))}}]}
    client.query(
  q.Map(
    q.Paginate(
      q.Join(
        q.Match(
          q.Index('spellbooks_by_owner'),
          q.Ref(q.Collection('characters'), '181388642114077184')
        ),
        q.Index('spells_by_spellbook'),
      )
    ),
    q.Lambda("ref", q.Get(q.Var("ref")))
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error('Error: %s', err))
    {
  data: [
    {
      ref: Ref(Collection("spells"), "181388642046968320"),
      ts: 1626225336060000,
      data: [Object]
    },
    {
      ref: Ref(Collection("spells"), "181388642071085568"),
      ts: 1626225336060000,
      data: [Object]
    }
  ]
}
    result = client.query(
  q.map_(
    q.lambda_("ref", q.get(q.var("ref"))),
    q.paginate(
      q.join(
        q.match(
          q.index("spellbooks_by_owner"),
          q.ref(q.collection("characters"), "181388642114077184")
        ),
        q.index("spells_by_spellbook")
      )
    )
  )
)
print(result)
    {'data': [{'ref': Ref(id=181388642046968320, collection=Ref(id=spells, collection=Ref(id=collections))), 'ts': 1626225336060000, 'data': {'name': 'Fire Beak', 'element': ['air', 'fire'], 'spellbook': Ref(id=181388642139243008, collection=Ref(id=spellbooks, collection=Ref(id=collections)))}}, {'ref': Ref(id=181388642071085568, collection=Ref(id=spells, collection=Ref(id=collections))), 'ts': 1626225336060000, 'data': {'name': "Water Dragon's Claw", 'element': ['water', 'fire'], 'spellbook': Ref(id=181388642139243008, collection=Ref(id=spellbooks, collection=Ref(id=collections)))}}]}
    try {
  println(Await.result(
    client.query(
      Map(
        Paginate(
          Join(
            Match(
              Index("spellbooks_by_owner"),
              Ref(Collection("characters"), "181388642114077184")
            ),
            Index("spells_by_spellbook")
          )
        ),
        Lambda("ref", Get(Var("ref")))
      )
    ),
    5.seconds
  ))
} catch {
  case unknown: Throwable => println("Error: " + unknown.getMessage())
}
    {data: [{ref: ref(id = "181388642046968320", collection = ref(id = "spells", collection = ref(id = "collections"))), ts: 1626225486300000, data: {name: "Fire Beak", element: ["air", "fire"], spellbook: ref(id = "181388642139243008", collection = ref(id = "spellbooks", collection = ref(id = "collections")))}}, {ref: ref(id = "181388642071085568", collection = ref(id = "spells", collection = ref(id = "collections"))), ts: 1626225486300000, data: {name: "Water Dragon's Claw", element: ["water", "fire"], spellbook: ref(id = "181388642139243008", collection = ref(id = "spellbooks", collection = ref(id = "collections")))}}]}
    Map(
  Paginate(
    Join(
      Match(
        Index('spellbooks_by_owner'),
        Ref(Collection('characters'), '181388642114077184')
      ),
      Index('spells_by_spellbook'),
    )
  ),
  Lambda("ref", Get(Var("ref")))
)
    {
  data: [
    {
      ref: Ref(Collection("spells"), "181388642046968320"),
      ts: 1626225336060000,
      data: {
        name: 'Fire Beak',
        element: [ 'air', 'fire' ],
        spellbook: Ref(Collection("spellbooks"), "181388642139243008")
      }
    },
    {
      ref: Ref(Collection("spells"), "181388642071085568"),
      ts: 1626225336060000,
      data: {
        name: "Water Dragon's Claw",
        element: [ 'water', 'fire' ],
        spellbook: Ref(Collection("spellbooks"), "181388642139243008")
      }
    }
  ]
}

  2. The following query joins the "spellbooks" documents for a specific owner with a Lambda function, which performs the equivalent "spells_by_spellbook" lookup as the previous query example:

    try
{
    Value result = await client.Query(
        Paginate(
            Join(
                Match(
                    Index("products_by_store"),
                    Ref(Collection("stores"), "301")
                ),
                Lambda(
                    Arr("name", "description", "price"),
                    Match(
                        Index("inventory_by_product"),
                        Var("name")
                    )
                )
            )
        )
    );
    Console.WriteLine(result);
}
catch (Exception e)
{
    Console.WriteLine($"ERROR: {e.Message}");
}
    ObjectV(data: Arr(Arr(LongV(1000), StringV(Conventional Hass, 4ct bag), RefV(id = "204", collection = RefV(id = "products", collection = RefV(id = "collections")))), Arr(LongV(1000), StringV(Conventional, 1 ct), RefV(id = "205", collection = RefV(id = "products", collection = RefV(id = "collections")))), Arr(LongV(100), StringV(Organic, 1 bunch), RefV(id = "208", collection = RefV(id = "products", collection = RefV(id = "collections")))), Arr(LongV(50), StringV(Organic, 16 oz bag), RefV(id = "206", collection = RefV(id = "products", collection = RefV(id = "collections")))), Arr(LongV(30), StringV(Conventional, 16 oz bag), RefV(id = "207", collection = RefV(id = "products", collection = RefV(id = "collections"))))))
    result, err := client.Query(
	f.Paginate(
		f.Join(
			f.MatchTerm(
				f.Index("products_by_store"),
				f.Ref(f.Collection("stores"), "301"),
			),
			f.Lambda(
				f.Arr{"name", "description", "price"},
				f.MatchTerm(
					f.Index("inventory_by_product"),
					f.Var("name"),
				),
			),
		),
	))

if err != nil {
	fmt.Fprintln(os.Stderr, err)
} else {
	fmt.Println(result)
}
    map[data:[[1000 Conventional Hass, 4ct bag {204 0xc000182300 0xc000182300 <nil>}] [1000 Conventional, 1 ct {205 0xc0001824b0 0xc0001824b0 <nil>}] [100 Organic, 1 bunch {208 0xc000182660 0xc000182660 <nil>}] [50 Organic, 16 oz bag {206 0xc000182810 0xc000182810 <nil>}] [30 Conventional, 16 oz bag {207 0xc0001829c0 0xc0001829c0 <nil>}]]]
    System.out.println(
    client.query(
        Paginate(
            Join(
                Match(
                    Index("products_by_store"),
                    Ref(Collection("stores"), "301")
                ),
                Lambda(
                    Arr(
                        Value("name"),
                        Value("description"),
                        Value("price")
                    ),
                    Match(
                        Index("inventory_by_product"),
                        Var("name")
                    )
                )
            )
        )
    ).get());
    {data: [[1000, "Conventional Hass, 4ct bag", ref(id = "204", collection = ref(id = "products", collection = ref(id = "collections")))], [1000, "Conventional, 1 ct", ref(id = "205", collection = ref(id = "products", collection = ref(id = "collections")))], [100, "Organic, 1 bunch", ref(id = "208", collection = ref(id = "products", collection = ref(id = "collections")))], [50, "Organic, 16 oz bag", ref(id = "206", collection = ref(id = "products", collection = ref(id = "collections")))], [30, "Conventional, 16 oz bag", ref(id = "207", collection = ref(id = "products", collection = ref(id = "collections")))]]}
    client.query(
  q.Paginate(
    q.Join(
      q.Match(
        q.Index('products_by_store'),
        q.Ref(q.Collection('stores'), '301'),
      ),
      q.Lambda(
        ['name', 'description', 'price'],
        q.Match(q.Index('inventory_by_product'), q.Var('name')),
      )
    )
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error('Error: %s', err))
    {
  data: [
    [
      1000,
      'Conventional Hass, 4ct bag',
      Ref(Collection("products"), "204")
    ],
    [ 1000, 'Conventional, 1 ct', Ref(Collection("products"), "205") ],
    [ 100, 'Organic, 1 bunch', Ref(Collection("products"), "208") ],
    [ 50, 'Organic, 16 oz bag', Ref(Collection("products"), "206") ],
    [
      30,
      'Conventional, 16 oz bag',
      Ref(Collection("products"), "207")
    ]
  ]
}
    result = client.query(
  q.paginate(
    q.join(
      q.match(
        q.index("products_by_store"),
        q.ref(q.collection("stores"), "301")
      ),
      q.lambda_(
        ["name", "description", "price"],
        q.match(
          q.index("inventory_by_product"),
          q.var("name")
        )
      )
    )
  )
)
print(result)
    {'data': [[1000, 'Conventional Hass, 4ct bag', Ref(id=204, collection=Ref(id=products, collection=Ref(id=collections)))], [1000, 'Conventional, 1 ct', Ref(id=205, collection=Ref(id=products, collection=Ref(id=collections)))], [100, 'Organic, 1 bunch', Ref(id=208, collection=Ref(id=products, collection=Ref(id=collections)))], [50, 'Organic, 16 oz bag', Ref(id=206, collection=Ref(id=products, collection=Ref(id=collections)))], [30, 'Conventional, 16 oz bag', Ref(id=207, collection=Ref(id=products, collection=Ref(id=collections)))]]}
    try {
  println(Await.result(
    client.query(
      Paginate(
        Join(
          Match(
            Index("products_by_store"),
            Ref(Collection("stores"), "301")
          ),
          Lambda(
            Arr("name", "description", "price"),
            Match(Index("inventory_by_product"), Var("name"))
          )
        )
      )
    ),
    5.seconds
  ))
} catch {
  case unknown: Throwable => println("Error: " + unknown.getMessage())
}
    {data: [[1000, "Conventional Hass, 4ct bag", ref(id = "204", collection = ref(id = "products", collection = ref(id = "collections")))], [1000, "Conventional, 1 ct", ref(id = "205", collection = ref(id = "products", collection = ref(id = "collections")))], [100, "Organic, 1 bunch", ref(id = "208", collection = ref(id = "products", collection = ref(id = "collections")))], [50, "Organic, 16 oz bag", ref(id = "206", collection = ref(id = "products", collection = ref(id = "collections")))], [30, "Conventional, 16 oz bag", ref(id = "207", collection = ref(id = "products", collection = ref(id = "collections")))]]}
    Paginate(
  Join(
    Match(
      Index('products_by_store'),
      Ref(Collection('stores'), '301'),
    ),
    Lambda(
      ['name', 'description', 'price'],
      Match(Index('inventory_by_product'), Var('name'))
    )
  )
)
    {
  data: [
    [
      1000,
      'Conventional Hass, 4ct bag',
      Ref(Collection("products"), "204")
    ],
    [ 1000, 'Conventional, 1 ct', Ref(Collection("products"), "205") ],
    [ 100, 'Organic, 1 bunch', Ref(Collection("products"), "208") ],
    [ 50, 'Organic, 16 oz bag', Ref(Collection("products"), "206") ],
    [
      30,
      'Conventional, 16 oz bag',
      Ref(Collection("products"), "207")
    ]
  ]
}

  3. The following query uses the Lambda variant of Join to answer the question "What are the inventory levels for the products in a particular store?".

    This question cannot be directly answered using the index variant of Join because the "products_by_store" index returns each product’s name, description, and price, and the "inventory_by_product" index uses only the product’s name in its terms definition (a mismatch in tuple sizes). The Lambda function calls Match with just the name value to resolve the mismatch:

    try
{
    Value result = await client.Query(
        Paginate(
            Join(
                Match(
                    Index("products_by_store"),
                    Ref(Collection("stores"), "301")
                ),
                Lambda(
                    Arr("name", "description", "price"),
                    Match(
                        Index("inventory_by_product"),
                        Var("name")
                    )
                )
            )
        )
    );
    Console.WriteLine(result);
}
catch (Exception e)
{
    Console.WriteLine($"ERROR: {e.Message}");
}
    ObjectV(data: Arr(Arr(LongV(1000), StringV(Conventional Hass, 4ct bag), RefV(id = "204", collection = RefV(id = "products", collection = RefV(id = "collections")))), Arr(LongV(1000), StringV(Conventional, 1 ct), RefV(id = "205", collection = RefV(id = "products", collection = RefV(id = "collections")))), Arr(LongV(100), StringV(Organic, 1 bunch), RefV(id = "208", collection = RefV(id = "products", collection = RefV(id = "collections")))), Arr(LongV(50), StringV(Organic, 16 oz bag), RefV(id = "206", collection = RefV(id = "products", collection = RefV(id = "collections")))), Arr(LongV(30), StringV(Conventional, 16 oz bag), RefV(id = "207", collection = RefV(id = "products", collection = RefV(id = "collections"))))))
    result, err := client.Query(
	f.Paginate(
		f.Join(
			f.MatchTerm(
				f.Index("products_by_store"),
				f.Ref(f.Collection("stores"), "301"),
			),
			f.Lambda(
				f.Arr{"name", "description", "price"},
				f.MatchTerm(
					f.Index("inventory_by_product"),
					f.Var("name"),
				),
			),
		),
	))

if err != nil {
	fmt.Fprintln(os.Stderr, err)
} else {
	fmt.Println(result)
}
    map[data:[[1000 Conventional Hass, 4ct bag {204 0xc000182300 0xc000182300 <nil>}] [1000 Conventional, 1 ct {205 0xc0001824b0 0xc0001824b0 <nil>}] [100 Organic, 1 bunch {208 0xc000182660 0xc000182660 <nil>}] [50 Organic, 16 oz bag {206 0xc000182810 0xc000182810 <nil>}] [30 Conventional, 16 oz bag {207 0xc0001829c0 0xc0001829c0 <nil>}]]]
    System.out.println(
    client.query(
        Paginate(
            Join(
                Match(
                    Index("products_by_store"),
                    Ref(Collection("stores"), "301")
                ),
                Lambda(
                    Arr(
                        Value("name"),
                        Value("description"),
                        Value("price")
                    ),
                    Match(
                        Index("inventory_by_product"),
                        Var("name")
                    )
                )
            )
        )
    ).get());
    {data: [[1000, "Conventional Hass, 4ct bag", ref(id = "204", collection = ref(id = "products", collection = ref(id = "collections")))], [1000, "Conventional, 1 ct", ref(id = "205", collection = ref(id = "products", collection = ref(id = "collections")))], [100, "Organic, 1 bunch", ref(id = "208", collection = ref(id = "products", collection = ref(id = "collections")))], [50, "Organic, 16 oz bag", ref(id = "206", collection = ref(id = "products", collection = ref(id = "collections")))], [30, "Conventional, 16 oz bag", ref(id = "207", collection = ref(id = "products", collection = ref(id = "collections")))]]}
    client.query(
  q.Paginate(
    q.Join(
      q.Match(
        q.Index('products_by_store'),
        q.Ref(q.Collection('stores'), '301'),
      ),
      q.Lambda(
        ['name', 'description', 'price'],
        q.Match(q.Index('inventory_by_product'), q.Var('name')),
      )
    )
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error('Error: %s', err))
    {
  data: [
    [
      1000,
      'Conventional Hass, 4ct bag',
      Ref(Collection("products"), "204")
    ],
    [ 1000, 'Conventional, 1 ct', Ref(Collection("products"), "205") ],
    [ 100, 'Organic, 1 bunch', Ref(Collection("products"), "208") ],
    [ 50, 'Organic, 16 oz bag', Ref(Collection("products"), "206") ],
    [
      30,
      'Conventional, 16 oz bag',
      Ref(Collection("products"), "207")
    ]
  ]
}
    result = client.query(
  q.paginate(
    q.join(
      q.match(
        q.index("products_by_store"),
        q.ref(q.collection("stores"), "301")
      ),
      q.lambda_(
        ["name", "description", "price"],
        q.match(
          q.index("inventory_by_product"),
          q.var("name")
        )
      )
    )
  )
)
print(result)
    {'data': [[1000, 'Conventional Hass, 4ct bag', Ref(id=204, collection=Ref(id=products, collection=Ref(id=collections)))], [1000, 'Conventional, 1 ct', Ref(id=205, collection=Ref(id=products, collection=Ref(id=collections)))], [100, 'Organic, 1 bunch', Ref(id=208, collection=Ref(id=products, collection=Ref(id=collections)))], [50, 'Organic, 16 oz bag', Ref(id=206, collection=Ref(id=products, collection=Ref(id=collections)))], [30, 'Conventional, 16 oz bag', Ref(id=207, collection=Ref(id=products, collection=Ref(id=collections)))]]}
    try {
  println(Await.result(
    client.query(
      Paginate(
        Join(
          Match(
            Index("products_by_store"),
            Ref(Collection("stores"), "301")
          ),
          Lambda(
            Arr("name", "description", "price"),
            Match(Index("inventory_by_product"), Var("name"))
          )
        )
      )
    ),
    5.seconds
  ))
} catch {
  case unknown: Throwable => println("Error: " + unknown.getMessage())
}
    {data: [[1000, "Conventional Hass, 4ct bag", ref(id = "204", collection = ref(id = "products", collection = ref(id = "collections")))], [1000, "Conventional, 1 ct", ref(id = "205", collection = ref(id = "products", collection = ref(id = "collections")))], [100, "Organic, 1 bunch", ref(id = "208", collection = ref(id = "products", collection = ref(id = "collections")))], [50, "Organic, 16 oz bag", ref(id = "206", collection = ref(id = "products", collection = ref(id = "collections")))], [30, "Conventional, 16 oz bag", ref(id = "207", collection = ref(id = "products", collection = ref(id = "collections")))]]}
    Paginate(
  Join(
    Match(
      Index('products_by_store'),
      Ref(Collection('stores'), '301'),
    ),
    Lambda(
      ['name', 'description', 'price'],
      Match(Index('inventory_by_product'), Var('name'))
    )
  )
)
    {
  data: [
    [
      1000,
      'Conventional Hass, 4ct bag',
      Ref(Collection("products"), "204")
    ],
    [ 1000, 'Conventional, 1 ct', Ref(Collection("products"), "205") ],
    [ 100, 'Organic, 1 bunch', Ref(Collection("products"), "208") ],
    [ 50, 'Organic, 16 oz bag', Ref(Collection("products"), "206") ],
    [
      30,
      'Conventional, 16 oz bag',
      Ref(Collection("products"), "207")
    ]
  ]
}

The run time of Join is dependent on the number of elements in the underlying set or page — it’s linear, or O(n). For very large sets or pages, executing Join might result in a query timeout error, or "width" error.

For query "width" errors, the underlying set or page involves more than 100K items. This can happen when using a set function, such as Difference, where more than 100K items need to be considered to produce the set that Join evaluates. To resolve this, use Paginate to limit the set or page size.

For example, instead of:

Join(
  Difference(
    Match(Index("Index1"), "term1"),
    Match(Index("Index2"), "term2")
  ),
  Index("Index3")
)

use:

Join(
  Paginate(
    Difference(
      Match(Index("Index1"), "term1"),
      Match(Index("Index2"), "term2")
    ),
    { size: 10000 }
  ),
  Index("Index3")
)

This does mean that if the entire set must be evaluated to arrive at the correct result, you would have to page through the Paginate results.

For query timeout errors, you may specify a larger query timeout via the driver that you are using.

Was this article helpful?

We're sorry to hear that.
Tell us how we can improve!
 Visit Fauna's Discourse forums or email docs@fauna.com

Thank you for your feedback!