Skip to main content

Documentation Index

Fetch the complete documentation index at: https://developer.affinity.co/llms.txt

Use this file to discover all available pages before exploring further.

​
All Persons on a List

View all persons on a list called “Distribution List”: 
SELECT
  *
FROM
  list_entries le
  JOIN lists l
    ON l.id = le.list_id
  JOIN list_entries_persons lep
    ON le.id = lep.list_entry_id
  JOIN persons p
    ON p.id = lep.person_id
WHERE
  l.name = 'Distribution List'
LIMIT 10;

​
All Persons Working at a Company Across Affinity Accounts

Find all persons associated with a given company across all subdomains: 
SELECT
  c.subdomain,
  c.name,
  c.domain,
  p.first_name,
  p.last_name
FROM companies c
JOIN companies_persons cp
  ON c.id = cp.company_id
JOIN persons p
  ON p.id = cp.person_id
WHERE
  c.name = 'Coralogix';

​
Flattening Custom Fields into Native Columns

Pivot a company’s custom fields JSON so each field becomes its own column: 
WITH base AS (
  SELECT
    id AS company_id,
    name,
    TRY_PARSE_JSON(custom_fields) AS cf
  FROM companies
  WHERE name = 'Klarna'
),
kv AS (
  SELECT
    b.company_id,
    b.name,
    k.value::STRING AS field_id,
    COALESCE(
      TRY_PARSE_JSON(GET(b.cf, k.value::STRING)::STRING),
      GET(b.cf, k.value::STRING)
    ) AS v_norm
  FROM base b,
    LATERAL FLATTEN(input => OBJECT_KEYS(b.cf)) k
),
exploded AS (
  SELECT
    company_id,
    name,
    field_id,
    v.value::STRING AS value_id
  FROM kv,
    LATERAL FLATTEN(input => IFF(IS_ARRAY(v_norm), v_norm, ARRAY_CONSTRUCT(v_norm))) v
),
res AS (
  SELECT
    e.company_id,
    e.name,
    m.name AS field_name,
    e.value_id,
    m.value_type,
    m.dropdown_options
  FROM exploded e
  JOIN fields_metadata m ON m.id = e.field_id
)
SELECT *
FROM res
PIVOT(MAX(value_id) FOR field_name IN (ANY ORDER BY field_name))
ORDER BY 1;
See Fields Metadata for details on the fields_metadata table.