Field Join

Since the relation between nodes in the same resource (tree) is already established, building a join table, doesn't require defining a relationship explicitly. FQL has a simplified field level join syntax for this purpose.

Syntax

    fieldjoin:  'left'? 'join' fielddecl

Functional

The field join takes a row R and a list-of-node L (the result of the fielddecl) as input, and produces a row T per L where the T will contain all nodes of R and L.

Usage

Example

This example will produce one row per given name per patient.

from Patient
select
   id,
   join name.given

Joining on multiple fields

To join multiple fields, the join statement can be used for each field.

select
   id,
   join name.given,
   join identifier

Joining on paired fields

In order to do a paired join, you have to create a temporary table: The approach for this is to join with a for ... query clause.

The following example does a join per name.

    from 
      Patient
    select
        id,
        join for name select { given, family }

Resulting in one row per name and only for patients with a name. A left join here would also produce a row if there is no name data. (FQL supports resource data with invalid cardinalities)

Unintended effects

The join can create some powerful and somefimes confusing effects when combined with other operations.

When joining with a group { }, you are effectively joining with a field list. So you will get one row for every field value in list.

from Patient
select
    id,
    join { name.given, name.family  }

When joining with a group-unwrap

from Patient
select
    id,
    join name { given, family }

since name { given, family } is a group-unwrap this can have unintended results. and will create one row per field produced by the unwrap.