`Join`

This reference topic applies to FQL v4. Go to this page for the latest FQL v10 reference topics.

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: it must not create side effects, such as any additional 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

Parameter Type Definition and Requirements

Parameter	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.

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

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

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] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))

{
  data: [
    {
      ref: Ref(Collection("spells"), "181388642046968320"),
      ts: 1632781965880000,
      data: {
        name: 'Fire Beak',
        element: [ 'air', 'fire' ],
        spellbook: Ref(Collection("spellbooks"), "181388642139243008")
      }
    },
    {
      ref: Ref(Collection("spells"), "181388642071085568"),
      ts: 1632781965880000,
      data: {
        name: "Water Dragon's Claw",
        element: [ 'water', 'fire' ],
        spellbook: Ref(Collection("spellbooks"), "181388642139243008")
      }
    }
  ]
}

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)))}}]}

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

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

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

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

Query metrics:

bytesIn: 239
bytesOut: 705
computeOps: 1
readOps: 3
writeOps: 0
readBytes: 418
writeBytes: 0
queryTime: 18ms
retries: 0

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:

client.query(
  q.Map(
    q.Paginate(
      q.Join(
        q.Match(
          q.Index('spellbooks_by_owner'),
          q.Ref(q.Collection('characters'), '181388642114077184'),
        ),
        q.Lambda(
          'spellbook',
          q.Match(q.Index('spells_by_spellbook'), q.Var('spellbook')),
        )
      )
    ),
    q.Lambda('ref', q.Get(q.Var('ref')))
  )
)
.then((ret) => console.log(ret))
.catch((err) => console.error(
  'Error: [%s] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))

{
  data: [
    {
      ref: Ref(Collection("spells"), "181388642046968320"),
      ts: 1632781965880000,
      data: {
        name: 'Fire Beak',
        element: [ 'air', 'fire' ],
        spellbook: Ref(Collection("spellbooks"), "181388642139243008")
      }
    },
    {
      ref: Ref(Collection("spells"), "181388642071085568"),
      ts: 1632781965880000,
      data: {
        name: "Water Dragon's Claw",
        element: [ 'water', 'fire' ],
        spellbook: Ref(Collection("spellbooks"), "181388642139243008")
      }
    }
  ]
}

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.lambda_(
          "spellbook",
          q.match(q.index("spells_by_spellbook"), q.var("spellbook"))
        )
      )
    )
  )
)
print(result)

{'data': [{'ref': Ref(id=181388642046968320, collection=Ref(id=spells, collection=Ref(id=collections))), 'ts': 1626224127300000, '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': 1626224127300000, 'data': {'name': "Water Dragon's Claw", 'element': ['water', 'fire'], 'spellbook': Ref(id=181388642139243008, collection=Ref(id=spellbooks, collection=Ref(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.Lambda(
					"spellbook",
					f.MatchTerm(
						f.Index("spells_by_spellbook"),
						f.Var("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 0xc000124510 0xc000124510 <nil>}] ref:{181388642046968320 0xc000124330 0xc000124330 <nil>} ts:1626224127300000] map[data:map[element:[water fire] name:Water Dragon's Claw spellbook:{181388642139243008 0xc0001248d0 0xc0001248d0 <nil>}] ref:{181388642071085568 0xc0001246f0 0xc0001246f0 <nil>} ts:1626224127300000]]]

try
{
    Value result = await client.Query(
        Map(
            Paginate(
                Join(
                    Match(
                        Index("spellbooks_by_owner"),
                        Ref(
                            Collection("characters"),
                            "181388642114077184"
                        )
                    ),
                    Lambda(
                        "spellbook",
                        Match(
                            Index("spells_by_spellbook"),
                            Var("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(1626224127300000),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(1626224127300000),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")))))))

System.out.println(
    client.query(
        Map(
            Paginate(
                Join(
                    Match(
                        Index("spellbooks_by_owner"),
                        Ref(Collection("characters"), "181388642114077184")
                    ),
                    Lambda(
                        Value("spellbook"),
                        Match(
                            Index("spells_by_spellbook"),
                            Var("spellbook")
                        )
                    )
                )
            ),
            Lambda("ref", Get(Var("ref")))
        )
    ).get());

{data: [{ref: ref(id = "181388642046968320", collection = ref(id = "spells", collection = ref(id = "collections"))), ts: 1626224127300000, 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: 1626224127300000, 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'),
      ),
      Lambda(
        'spellbook',
        Match(Index('spells_by_spellbook'), Var('spellbook')),
      )
    )
  ),
  Lambda("ref", Get(Var("ref")))
)

{
  data: [
    {
      ref: Ref(Collection("spells"), "181388642046968320"),
      ts: 1626221552780000,
      data: {
        name: 'Fire Beak',
        element: [ 'air', 'fire' ],
        spellbook: Ref(Collection("spellbooks"), "181388642139243008")
      }
    },
    {
      ref: Ref(Collection("spells"), "181388642071085568"),
      ts: 1626221552780000,
      data: {
        name: "Water Dragon's Claw",
        element: [ 'water', 'fire' ],
        spellbook: Ref(Collection("spellbooks"), "181388642139243008")
      }
    }
  ]
}

Query metrics:

bytesIn: 307
bytesOut: 705
computeOps: 1
readOps: 3
writeOps: 0
readBytes: 418
writeBytes: 0
queryTime: 12ms
retries: 0

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:

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] %s: %s',
  err.name,
  err.message,
  err.errors()[0].description,
))

{
  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)))]]}

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

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

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

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

Query metrics:

bytesIn: 234
bytesOut: 694
computeOps: 1
readOps: 1
writeOps: 0
readBytes: 811
writeBytes: 0
queryTime: 24ms
retries: 0

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.