Regulatory mapping reference

The mapping is a YAML document. It says where each value of a regulatory document comes from in your data. To send it, replace the mapping of a subscription. Reporter reads the document, checks it against the contract of the pinned version, and stores it. The contract of a document and its template belong to Lerian. The contract names the sets and fields of the document. The template places each value in the file. This page describes the mapping. A mapping that Reporter cannot read is refused with HTTP 400 and RPT-0143. The detail names the point to correct, for example query 'saldos_cosif'. See the error list.

Example

This mapping fills two sets of CADOC 4010. The query saldos_cosif adds up the approved entries of each route_code that were created before the end of the period of the parameter dataBase. A credit adds its amount. A debit subtracts it. The published reference mapping for this document also filters on the BRL asset, on entries that moved a balance, and on rows that are not deleted. It also cuts the end of the period by the business calendar.

kind: DocumentBinding
contract: br.bcb.cadoc-4010
contractVersion: 1.0.0

queries:
  - name: declarante
    dataSource: midaz_onboarding
    table: organization
    filters:
      - field: deleted_at
        operator: isNull

  - name: saldos_cosif
    dataSource: midaz_transaction
    table: operation
    filters:
      - field: status
        operator: eq
        values: ["APPROVED"]
      - field: created_at
        operator: lt
        values: [{parameter: dataBase, at: end}]
    compute:
      - as: valor_no_razao
        case:
          column: direction
          when:
            credit: {column: amount}
            debit: {negate: amount}
          otherwise: refuse
    aggregate:
      groupBy: [route_code]
      measures:
        - as: saldo
          fn: sum
          of: valor_no_razao

datasets:
  institution:
    from:
      query: declarante
    fields:
      cnpj:
        from: query
        column: legal_document

  cosif_balances:
    from:
      query: saldos_cosif
    fields:
      cosifCode:
        from: query
        column: route_code
      saldo:
        from: query
        column: saldo

How Reporter reads the document

The document must be YAML. An empty document is refused. The top level must be an object of keys and values.
Each block takes the keys in its table on this page. A block with any other key is refused. The refusal lists the keys that the block takes.
A known key with no value counts as not written.
A required text cannot be empty.
Reporter reads a value as the text that you write. 7 and "7" are the same value.
Reporter accepts YAML anchors and aliases. It refuses the merge key <<.
Two queries cannot share a name. Two sets cannot share a name. Two fields of a set cannot share a name.
These names must match a query of the mapping: the from of a query, the query of the from of a set, the query of a join, and the query of a field of a set. A query can be declared after the set or query that refers to it.

Top-level keys

Key	Type	Required	What it does
`apiVersion`	text	No	Format marker.
`kind`	text	No	Format marker. When you write it, the value must be `DocumentBinding`.
`contract`	text	Yes	ID of the document that the mapping fills, for example `br.bcb.cadoc-4010`.
`contractVersion`	text	No	Version of the contract that you wrote the mapping against.
`queries`	list of queries	No	The reads of your data.
`datasets`	object of sets	Yes	How each set of the document is filled. `datasets: {}` is accepted and maps nothing. A document with no `datasets` key is refused.
`manualOverrides`	list of overrides	No	Values that Reporter checks on save and stores with the mapping. To change a value in a filing, correct the draft.
`generatable`	`true` or `false`	No	Your declaration of whether the document can be generated.
`incompleteReason`	text	No	Why the document cannot be generated.
`incompleteFields`	list of text	No	Fields that have no source, each written as `set.field`.

Queries

A query reads rows from your data. Sets and other queries refer to a query by its name.

Key	Type	Required	What it does
`name`	text	Yes	Names the query.
`description`	text	No	A description of the query.
`dataSource`	text	With `table`	The `configName` of the data source that the query reads.
`table`	text	With `dataSource`	Key of the table in that data source.
`from`	text	Instead of `dataSource` and `table`	Another query to read. See the constructs that a generation refuses.
`expand`	text	No	List inside each document whose items become rows.
`filters`	list of conditions	No	Keeps the rows that pass, before grouping.
`having`	list of conditions	No	See the constructs that a generation refuses.
`join`	list of joins	No	Brings a second table or query next to the rows.
`derive`	list of derived columns	No	See the constructs that a generation refuses.
`compute`	list of computed columns	No	Adds a column that a case computes on each row.
`aggregate`	object	No	Groups the rows and computes measures.
`select`	list of text	No	Names the columns that the query returns.

A query is refused when:

it has neither dataSource nor from;
it has from and also dataSource or table;
it has dataSource and no table;
its from names itself, names a query that the mapping does not declare, or closes a ring with other queries.

Filters

A row stays when it passes each condition of filters. Conditions on the same column all apply. A condition is a test or a combination:

Key	Type	What it does
`field`	text	The column to test.
`operator`	text	How to compare.
`values`	list	What to compare with.
`anyOf`	list of conditions	A combination. See the constructs that a generation refuses.
`allOf`	list of conditions	A combination. See the constructs that a generation refuses.

A condition needs field and operator, or anyOf or allOf. These operators are accepted:

Operator	Values
`eq`, `in`, `nin`	One or more values, or one parameter.
`gt`, `gte`, `lt`, `lte`	One value, or one parameter.
`startsWith`, `notStartsWith`	One or more values, or one parameter. Each value is a prefix.
`between`	Exactly two values: the bounds. A parameter is not accepted.
`isNull`, `notNull`	No values and no parameter.

To exclude rows by prefix, use notStartsWith. This filter drops the rows whose route_code starts with 7 or 8:

filters:
  - field: route_code
    operator: notStartsWith
    values: ["7", "8"]

A condition is refused when:

it has a key other than field, operator, values, anyOf, and allOf;
it has field and also anyOf or allOf;
it has neither field nor anyOf or allOf;
it has field and no operator;
its operator is not in the table. The refusal lists the accepted operators;
it has isNull or notNull with values or a parameter;
it has between with a parameter, or with a number of values other than two;
it has any other operator with no value and no parameter.

Two conditions of filters on one column are refused when they use the same operator. They are also refused when one of them is isNull or notNull. Two different operators on one column combine, which is how you write a period: gte for its start and lt for its end. A generation refuses between beside gte or lte.

Values read from a parameter

A parameter is a value that you send in the generation request. An item of values that is an object reads one parameter:

Key	Type	Required	What it does
`parameter`	text	Yes	Name of the parameter.
`at`	`start` or `end`	No	The edge of the period that the parameter names.
`of`	`day`, `month`, or `year`	No	The period that the edge belongs to. It needs `at`.
`calendar`	`business`	No	Cuts the period by the business days of the contract.

Without at, the condition compares with the value of the parameter as the request carries it. With at, the parameter names a period: AAAA-MM for a month, AAAA-MM-DD for a day. The edges follow the report time zone. start is the first instant inside the period. end is the first instant after it. Compare start with gte and end with lt. No instant then falls in two periods or in none. With calendar: business, a period ends right after its last business day. It starts right after the last business day of the period before. This query reads the month of the parameter, up to the end of its period:

filters:
  - field: created_at
    operator: gte
    values: [{parameter: dataBase, at: start, of: month}]
  - field: created_at
    operator: lt
    values: [{parameter: dataBase, at: end}]

A parameter value is refused when:

values lists two parameters, or lists a text beside a parameter;
the object of the parameter has a key other than parameter, at, of, and calendar, or has no parameter;
at is not start or end;
of is not day, month, or year;
calendar is not business;
it has of or calendar and no at;
it has at: start with an operator other than gte, or at: end with an operator other than lt.

A generation refuses an of that is shorter than the period of the parameter, for example of: day on a month.

Joins

A join brings a table next to the rows of the query. Each item of join has these keys:

Key	Type	Required	What it does
`table`	text	One of `table` or `query`	The table to bring.
`query`	text	One of `table` or `query`	The query to bring. See the constructs that a generation refuses.
`match`	object	Yes	Pairs a column of this query (key) with a column of the joined table or query (value).
`columns`	object	With `table`	The columns that the join brings. Each key is the name that this query uses. Each value is the column of the joined table. Other columns stay out.
`type`	text	No	`left` or `inner`. The default is `left`.

With left, each row of the query stays, and the brought columns are empty where no row pairs. With inner, the rows without a partner leave. A row with several partners becomes several rows. A join is refused when:

it names neither table nor query, or names both;
it has no match, or match has no pair;
it writes on in place of match. Write match;
it has a key other than table, query, match, columns, and type;
it brings a table and has no columns, or columns has no pair;
it brings a query and has columns;
two joins of the query bring the same name, or a join brings a name that it also pairs on in match;
type is not left or inner.

Computed columns

compute adds a column to each row. The steps after it read it like a column of the table: filters, grouping, and sums.

Key	Type	Required	What it does
`as`	text	Yes	Name of the new column.
`case`	object	Yes	Picks the value of the column.

A case has these keys:

Key	Required	What it does
`column`	Yes	The column whose value picks the answer.
`when`	Yes	An object. Each key is a value of `column`. Each value is an answer.
`otherwise`	Yes	The answer for a value that `when` does not list. It is `refuse` or an answer.

An answer is an object with exactly one key:

Key	The value of the new column
`column`	The value of that column.
`negate`	The value of that column with its sign changed.
`constant`	The text that you write.

With otherwise: refuse, a row whose value when does not list refuses the generation. A computed column is refused when:

compute has two items with the same as;
an item has a key other than as and case, or lacks as or case;
the case has a key other than column, when, and otherwise;
column or otherwise is missing, or when is missing or empty;
otherwise is a text other than refuse;
as is also a name that a join brings or a derive names, or the case reads another computed column;
an answer does not have exactly one of column, negate, and constant, or its value is empty.

Grouping and measures

aggregate folds the rows into one row for each group.

Key	Type	Required	What it does
`groupBy`	list of text	No	Columns that define a group. Without it, all rows fold into one row.
`measures`	list of measures	Yes	Values that Reporter computes for each group.

A measure has these keys. All three are required:

Key	What it does
`as`	Name of the result. A field reads the result under this name.
`fn`	The function. Write `sum`. Reporter refuses another function when it generates a filing.
`of`	The column to add up. It can be a column of the table, a joined column, or a computed column.

A query that groups returns the groupBy columns and one column for each measure. A field that reads another column is refused when you save. A generation is refused when a row has no value in a groupBy column. aggregate is refused when it has a key other than groupBy and measures, or measures is missing or empty. A measure is refused when it has a key other than as, fn, and of, or lacks one of them. A generation refuses two measures with the same as.

Columns that a query returns

select names the columns that a query returns. Without it, the query returns each column and the fields choose. With it, a field that reads another column is refused when you save. select is refused when the query also has aggregate, when it is empty, or when it names a column twice or names an empty column.

Lists inside documents

expand names a list inside each document, as a dotted path of the fields of the table. Each item of the list becomes a row. The query reads the fields of the item under the path of the list. On a PostgreSQL data source, the answer of the save lists expand under unexecuted, and a generation is refused. expand is refused when the path is empty, starts or ends with a dot, has two dots in a row, or has a space or a $. It is also refused on a query that has from.

Constructs that a generation refuses

Reporter reads and stores these constructs. The answer of the save lists them under unsupported. A generation of a mapping that holds one is refused.

Construct	How you write it
A query that reads another query	`from` on a query
Narrowing after grouping	`having`
A derived column	`derive`, with `as`, `from`, and `apply`, all required
A join of a query	`query` in a join
Combined conditions	`anyOf` or `allOf` in `filters`

Sets

Each key of datasets is the name of a set that the contract declares. Its value has these keys:

Key	Type	Required	What it does
`from`	object	No	Has one key, `query`. It names the query whose rows become the rows of the set.
`rowKey`	object	No	For a set whose rows the catalog publishes. Pairs the field that identifies a published row (key) with the column of the query that identifies the same row (value).
`parentMatch`	object	No	For a set that the contract declares as a child of another set. Pairs a field of the parent set (key) with the column of the query that carries it (value).
`fields`	object of fields	Yes	Where each field of the set gets its value. It cannot be empty.
`also`	list of sources	No	Other sources of the same set. See Consolidation.

An open set has one row for each row of its query. Reporter sorts the rows by the key fields of the set, as text in byte order. Without from, the set has no query rows. If the contract declares the set as one row, its constants and parameters fill that row. When the contract declares a hierarchy for the code of the set, Reporter also adds a row for each ancestor code, with the sum of the rows beneath it. Return the amount posted to each code. Do not return a parent amount that already includes its children, because Reporter adds the children to it. The rows of a set that the catalog publishes are the published rows, one for each. The mapping gives their values:

The queries of the set read only the rows whose rowKey column holds a published code. When the contract declares a hierarchy for the code, they read the rows whose code falls under a published code. Reporter leaves out a row with another code and does not refuse it. In an item of also, Reporter does not leave out a row with another code: it refuses the generation.
A result row fills the published row of its code. When the contract declares a hierarchy, it also fills each published row above that code. Such a published row reads the sum of the measures of the result rows at and below its code.
With a hierarchy, return the amount posted to each code. Do not return a parent amount that already includes its children, because Reporter adds the children to it.
A published row with no result row reads 0 in a measure that a query fills. Its other query columns get no value. A required field that reads one refuses the generation.
Two result rows with the same code refuse the generation.
A generation where no result row matches a published row is refused.

A set is refused when:

its from is not an object with a query key;
it has a key other than from, rowKey, parentMatch, fields, and also;
rowKey or parentMatch has no pair;
it has no fields, or fields is empty;
its from names a query that the mapping does not declare.

Fields

A field says where its value comes from. Write one source, or write byRowClass.

Key	Type	What it does
`from`	text	The origin of the value: `query`, `constant`, `parameter`, `manual`, or `rowSet`.
`column`	text	The column that Reporter reads.
`query`	text	The query that Reporter reads, when it is not the query of the set.
`value`	text	The fixed value.
`parameter`	text	The name of the parameter.
`pendingReason`	text	Why a manual field has no value.
`byRowClass`	list of cases	Chooses the source by the row.

Each origin needs one thing:

`from`	Needs	The value of the field
`query`	`column`	The `column` of the row of the query. In a set whose rows the catalog publishes, `query` names the query to read in place of the query of the set. Write a field’s own `query` key only in such a set. A generation is refused when the query returns no such column.
`constant`	`value`	The `value`.
`parameter`	`parameter`	The value of that parameter in the generation request. When the request leaves it out, Reporter uses the default of the contract. With no default, a parameter that the contract requires refuses the request. Name a parameter that the contract declares.
`manual`	`value` or `pendingReason`	The `value`, when you write it. Without a value, the field stays blank for a person to fill in the draft. The `pendingReason` tells that person why.
`rowSet`	`column`	The `column` of the published row.

A field of the contract that the mapping does not name stays blank, and the answer lists it as a gap. Reporter refuses to approve a draft while a required field is blank. A field source is refused when:

from is missing, or is not one of the five origins;
it names a query and its origin is not query;
the origin is query or rowSet and there is no column;
the origin is constant and there is no value;
the origin is parameter and there is no parameter;
the origin is manual and there is neither a value nor a pendingReason;
the field has a key other than the keys in the table above;
the query that it names is not declared by the mapping.

Sources that depend on the row

byRowClass is for a set whose rows the catalog publishes. Each case has when and the keys of a source. The first case whose when matches the published row gives the value. A row that no case matches gets no value from this field. The generation is refused when the contract requires the field. when has two required keys: rowSet, the column of the published row, and equals, the value that it must have. This field reads a balance for one class of row and a monthly total for the other. Each class reads its own query:

saldoDia:
  byRowClass:
    - when: { rowSet: measure, equals: stock }
      from: query
      column: saldoDia
    - when: { rowSet: measure, equals: flow }
      from: query
      query: volumes_fluxo
      column: volumeMes

A byRowClass is refused when the field also has a from of its own. A case is refused when it has no when, when when has a key other than rowSet and equals, when rowSet or equals is missing, or when it has a key other than when and the keys of a source.

Consolidation

also adds sources to a set. Each item maps the same set over another query, for example another database of the filing institution. Reporter does not limit the query of an item to the filing institution. The filters of that query must choose the rows of the filing institution. The contract of the document says how Reporter joins the rows of the sources, and which sets accept also. An item has the keys from, rowKey, and fields. It has no parentMatch and no also. Its from is required. Its fields use the keys of the fields of a set. An item with no fields, or with empty fields, maps nothing, and each field it does not map is a gap. Reporter refuses an item whose from is also the from of the set or of another item. It refuses an item that reads a query that the mapping of any set reads. It also refuses an item that maps a field that the set’s own mapping does not map. Give each item its own queries.

Manual overrides and declarations

Reporter checks these keys when you save the mapping, and stores them with it. Each item of manualOverrides has these keys:

Key	Type	Required	Meaning
`field`	text	Yes	The field, written as `set.field`.
`value`	text	Yes	The value.
`reason`	text	No	Why you wrote it.

An item is refused when it has a key other than these three, or when field or value is empty. generatable, incompleteReason, and incompleteFields hold your declaration about the document:

generatable must be true or false.
generatable: false is refused when incompleteReason is missing.
incompleteFields is refused when it lists a field and generatable is not false.

Checks against the contract

After the document is read, Reporter compares it with the contract of the pinned version. The save is refused when:

The mapping	What you change
Names a `contract` other than the document of the subscription.	Write the ID of the document.
Fills a set or a field that the contract does not declare, or maps a field that the contract derives from another field.	Use the sets and fields of the contract. Map the field that the derived field comes from.
Compares a column with a parameter that the contract does not declare, uses `at` on a parameter that is not a month or a date, or uses `calendar: business` when the contract has no calendar.	Use a parameter of the contract.
Reads a column from a query that groups, in a field, `rowKey`, or `parentMatch`, and the query does not return it.	Name a `groupBy` column or the `as` of a measure.
Fills a set whose rows the catalog publishes from a query and has no `rowKey`, or pairs a field other than the one that identifies the rows.	Pair the field that identifies the published rows.
Uses `rowKey` or `from: rowSet` on a set with no published rows.	Remove `rowKey`, and read each value from another origin.
Fills a child set from a query and does not pair each key of the parent in `parentMatch`, or uses `parentMatch` on a set that is not a child.	Pair each key of the parent.
Uses `also` on a set that the contract does not declare for consolidation.	Remove `also`.

Overview

Concepts

Guides

Integration

Console

Operations

Regulatory mapping reference

Example

How Reporter reads the document

Top-level keys

Queries

Filters

Values read from a parameter

Joins

Computed columns

Grouping and measures

Columns that a query returns

Lists inside documents

Constructs that a generation refuses

Sets

Fields

Sources that depend on the row

Consolidation

Manual overrides and declarations

Checks against the contract

​Example

​How Reporter reads the document

​Top-level keys

​Queries

​Filters

​Values read from a parameter

​Joins

​Computed columns

​Grouping and measures

​Columns that a query returns

​Lists inside documents

​Constructs that a generation refuses

​Sets

​Fields

​Sources that depend on the row

​Consolidation

​Manual overrides and declarations

​Checks against the contract

Example

How Reporter reads the document

Top-level keys

Queries

Filters

Values read from a parameter

Joins

Computed columns

Grouping and measures

Columns that a query returns

Lists inside documents

Constructs that a generation refuses

Sets

Fields

Sources that depend on the row

Consolidation

Manual overrides and declarations

Checks against the contract