Database tables reference

Introduction

How tables are created

The add command creates all the tables below.

How values are extracted

Most values are extracted from OCDS JSON as SQL text. This is the case even if the JSON value is of a different type; for example, if the value of an id field is serialized as a JSON integer, it will be stored as text in the SQL tables.

There are two cases in which other types are used:

  • Date fields are converted to the timestamp type. Warning: If the value is an invalid date like "2020-02-30", or if the year is less than or equal to 0000, it will be converted to NULL.

  • Number fields are converted to the numeric type. Warning: If the value is an invalid number like "123a", it will be converted to NULL.

Metadata

These tables are created and populated by the add command.

selected_collections

This table contains the collection IDs that the user provided when creating the schema.

If you need to change the collections to be summarized, remove the schema with the remove command and re-create it with the add command. This ensures that the schema’s name reflects its contents.

Column Name

Data Type

Description

id

integer

A Kingfisher Process collection ID

note

This table contains the note that the user provided when creating the schema.

Column Name

Data Type

Description

id

integer

An auto-incrementing integer

note

text

A user-assigned note

created_at

timestamp

The UTC time at which the note was created

Fields

field_counts

Column Name

Data Type

Description

collection_id

bigint

id from the kingfisher collection table

release_type

text

Either release, compiled_release or record. compiled_release are releases generated by kingfisher release compilation

path

text

JSON path of the field

object_property

bigint

The total number of times the field at this path appears

array_count

bigint

For arrays, the total number of items in this array across all releases

distinct_releases

bigint

The total number of distinct releases in which the field at this path appears

Releases

release_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

table_id

integer

id from either release, compiled_release or release tables in Kingfisher Process where this row was generated from

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

package_data_id

integer

id from package_data table

package_version

text

OCDS version gathered from the version field in package

release_date

timestamp

date field from release

release_tag

jsonb

JSONB list of tags field from release

release_language

text

language field from release object

role_counts

jsonb

JSONB object with the keys as unique roles and the values as count of the appearances of those roles

total_roles

numeric

Total amount of roles specified across all parties

total_parties

bigint

Count of parties

total_planning_documents

bigint

Count of planning documents

planning_documenttype_counts

jsonb

Count of planning document types

total_tender_documents

bigint

Count of tender documents

tender_documenttype_counts

jsonb

Count of tender document types

award_count

bigint

Count of awards

first_award_date

timestamp

Earliest date in all award objects

last_award_date

timestamp

Latest date in all award objects

total_award_documents

numeric

The sum of documents_count for each award in this release

total_award_items

numeric

Count of all items in all awards

total_award_suppliers

bigint

The sum of suppliers_count for each award in this release

award_amount

numeric

Total of all value/amount across awards. NOTE: This ignores the fact that amounts could be of different currencies and sums them anyway

unique_award_suppliers

bigint

A count of distinct suppliers for all awards for this release, based on the unique_identifier_attempt field

award_documenttype_counts

jsonb

JSONB object with the keys as unique awards/documents/documentType and the values as count of the appearances of those documentTypes

contract_count

bigint

Count of contracts

total_contract_link_to_awards

bigint

Count of all contracts that have link to awards through awardID field

contract_amount

numeric

Total of all value/amount across contracts. NOTE: This ignores the fact that amounts could be of different currencies and sums them anyway

first_contract_datesigned

timestamp

First dateSigned across all contracts

last_contract_datesigned

timestamp

Last dateSigned across all contracts

total_contract_documents

numeric

Count of contracts/documents

total_contract_milestones

numeric

Count of contracts/milestones

total_contract_items

numeric

Count of contracts/items

total_contract_implementation_documents

numeric

Count of contracts/implementation/documents

total_contract_implementation_milestones

numeric

Count of contracts/implementation/milestones

contract_documenttype_counts

jsonb

JSONB object with the keys as unique contracts/documents/documentType and the values as count of the appearances of those documentTypes

contract_implementation_documenttype_counts

jsonb

JSONB object with the keys as unique contracts/implementation/documents/documentType and the values as count of the appearances of those documentTypes

contract_milestonetype_counts

jsonb

JSONB object with the keys as unique contracts/milestone/milestoneType and the values as count of the appearances of those milestoneTypes

contract_implementation_milestonetype_counts

jsonb

JSONB object with the keys as unique contracts/implementation/documents/milestoneType and the values as count of the appearances of those milestoneTypes

total_documenttype_counts

jsonb

JSONB object with the keys as unique documentTypes from all documents in the release and the values as count of the appearances of those documentTypes

total_documents

numeric

Count of documents in the release

milestonetype_counts

jsonb

JSONB object with the keys as unique milestoneTypes from all milestones in the release and the values as count of the appearances of those milestoneTypes

total_milestones

numeric

Count of milestones in the release

source_id

text

source_id from Kingfisher Process collection table

data_version

timestamp

data_version from Kingfisher Process collection table

store_start_at

timestamp

store_start_at from Kingfisher Process collection table

store_end_at

timestamp

store_end_at from Kingfisher Process collection table

sample

boolean

sample from Kingfisher Process collection table

transform_type

text

transform_type from Kingfisher Process collection table

transform_from_collection_id

integer

transform_from_collection_id from Kingfisher Process collection table

deleted_at

timestamp

deleted_at from Kingfisher Process collection table

release

jsonb

data from Kingfisher Process data table. This is the whole release in JSONB

package_data

jsonb

data from Kingfisher Process package_data table. This is the package data in either a release or record package. For compiled releases generated by Kingfisher Process this is NULL

release_check

jsonb

JSONB of Data Review Tool output which includes validation errors and additional field information

release_check11

jsonb

JSONB of Data Review Tool output run against 1.1 version of OCDS even if the data is from 1.0

record_check

jsonb

JSONB of Data Review Tool output which includes validation errors and additional field information

record_check11

jsonb

JSONB of Data Review Tool output run against 1.1 version of OCDS even if the data is from 1.0

field_list

jsonb

All JSON paths in the release object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

release_summary_no_data

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

table_id

integer

id from either release, compiled_release or release tables in Kingfisher Process where this row was generated from

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

package_data_id

integer

id from package_data table

package_version

text

OCDS version gathered from the version field in package

release_date

timestamp

date field from release

release_tag

jsonb

JSONB list of tags field from release

release_language

text

language field from release object

role_counts

jsonb

JSONB object with the keys as unique roles and the values as count of the appearances of those roles

total_roles

numeric

Total amount of roles specified across all parties

total_parties

bigint

Count of parties

total_planning_documents

bigint

Count of planning documents

planning_documenttype_counts

jsonb

Count of planning document types

total_tender_documents

bigint

Count of tender documents

tender_documenttype_counts

jsonb

Count of tender document types

award_count

bigint

Count of awards

first_award_date

timestamp

Earliest date in all award objects

last_award_date

timestamp

Latest date in all award objects

total_award_documents

numeric

The sum of documents_count for each award in this release

total_award_items

numeric

Count of all items in all awards

total_award_suppliers

bigint

The sum of suppliers_count for each award in this release

award_amount

numeric

Total of all value/amount across awards. NOTE: This ignores the fact that amounts could be of different currencies and sums them anyway

unique_award_suppliers

bigint

A count of distinct suppliers for all awards for this release, based on the unique_identifier_attempt field

award_documenttype_counts

jsonb

JSONB object with the keys as unique awards/documents/documentType and the values as count of the appearances of those documentTypes

contract_count

bigint

Count of contracts

total_contract_link_to_awards

bigint

Count of all contracts that have link to awards through awardID field

contract_amount

numeric

Total of all value/amount across contracts. NOTE: This ignores the fact that amounts could be of different currencies and sums them anyway

first_contract_datesigned

timestamp

First dateSigned across all contracts

last_contract_datesigned

timestamp

Last dateSigned across all contracts

total_contract_documents

numeric

Count of contracts/documents

total_contract_milestones

numeric

Count of contracts/milestones

total_contract_items

numeric

Count of contracts/items

total_contract_implementation_documents

numeric

Count of contracts/implementation/documents

total_contract_implementation_milestones

numeric

Count of contracts/implementation/milestones

contract_documenttype_counts

jsonb

JSONB object with the keys as unique contracts/documents/documentType and the values as count of the appearances of those documentTypes

contract_implementation_documenttype_counts

jsonb

JSONB object with the keys as unique contracts/implementation/documents/documentType and the values as count of the appearances of those documentTypes

contract_milestonetype_counts

jsonb

JSONB object with the keys as unique contracts/milestone/milestoneType and the values as count of the appearances of those milestoneTypes

contract_implementation_milestonetype_counts

jsonb

JSONB object with the keys as unique contracts/implementation/documents/milestoneType and the values as count of the appearances of those milestoneTypes

total_documenttype_counts

jsonb

JSONB object with the keys as unique documentTypes from all documents in the release and the values as count of the appearances of those documentTypes

total_documents

numeric

Count of documents in the release

milestonetype_counts

jsonb

JSONB object with the keys as unique milestoneTypes from all milestones in the release and the values as count of the appearances of those milestoneTypes

total_milestones

numeric

Count of milestones in the release

Parties

parties_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

party_index

bigint

Position of the party in the parties array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

parties_id

text

id in party object

roles

jsonb

JSONB list of the party roles

identifier

text

Concatenation of scheme and id from identifier object in the form <scheme>-<id>

unique_identifier_attempt

text

The id from party object if it exists, otherwise the identifier described above if it exists, otherwise the party name

parties_additionalidentifiers_ids

jsonb

JSONB list of the concatenation of scheme and id of all additionalIdentifier objects

parties_additionalidentifiers_count

integer

Count of additional identifiers

party

jsonb

JSONB of party object

field_list

jsonb

All JSON paths in the party object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

buyer_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

buyer

jsonb

JSONB of buyer object

buyer_parties_id

text

id from buyer object

buyer_name

text

name from buyer object

buyer_identifier

text

Concatenation of scheme and id from identifier object in the form <scheme>-<id>

unique_identifier_attempt

text

The id from buyer object if it exists, otherwise the identifier described above if it exists, otherwise the party name

buyer_additionalidentifiers_ids

jsonb

JSONB list of the concatenation of scheme and id of all additionalIdentifier objects

buyer_additionalidentifiers_count

integer

Count of additional identifiers

link_to_parties

integer

Does this buyer link to a party in the parties array using the id field from buyer object linking to the id field in a party object? If this is true then 1, otherwise 0

link_with_role

integer

If there is a link does the parties object have buyer in its roles list? If it does then 1 otherwise 0

party_index

bigint

If there is a link what is the index of the party in the parties array then this can be used for joining to the parties_summary table

field_list

jsonb

All JSON paths in the buyer object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

procuringEntity_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

procuringentity

jsonb

JSONB of procuringEntity object

procuringentity_parties_id

text

id from procuringEntity object

procuringentity_identifier

text

Concatenation of scheme and id from identifier object in the form <scheme>-<id>

unique_identifier_attempt

text

The id from procuringEntity object if it exists, otherwise the identifier described above if it exists, otherwise the party name

procuringentity_additionalidentifiers_ids

jsonb

JSONB list of the concatenation of scheme and id of all additionalIdentifier objects

procuringentity_additionalidentifiers_count

integer

Count of additional identifiers

link_to_parties

integer

Does this procuringEntity link to a party in the parties array using the id field from buyer object linking to the id field in a party object? If this is true then 1, otherwise 0

link_with_role

integer

If there is a link does the parties object have procuringEntity in its roles list? If it does then 1 otherwise 0

party_index

bigint

If there is a link what is the index of the party in the parties array then this can be used for joining to the parties_summary table

field_list

jsonb

All JSON paths in the procuringentity object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

tenderers_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

tenderer_index

bigint

Position of the tenderer in the tenderer array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

tenderer

jsonb

JSONB of tenderer object

tenderer_parties_id

text

id from tenderer object

tenderer_identifier

text

Concatenation of scheme and id from identifier object in the form <scheme>-<id>

unique_identifier_attempt

text

The id from tenderer object if it exists, otherwise the identifier described above if it exists, otherwise the party name

tenderer_additionalidentifiers_ids

jsonb

JSONB list of the concatenation of scheme and id of all additionalIdentifier objects

tenderer_additionalidentifiers_count

integer

Count of additional identifiers

link_to_parties

integer

Does this tenderer link to a party in the parties array using the id field from buyer object linking to the id field in a party object? If this is true then 1, otherwise 0

link_with_role

integer

If there is a link does the parties object have tenderers in its roles list? If it does then 1 otherwise 0

party_index

bigint

If there is a link what is the index of the party in the parties array. This can be used for joining to the parties_summary table

field_list

jsonb

All JSON paths in the tenderer object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

Planning section

planning_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

planning_budget_amount

numeric

amount/amount from budget object

planning_budget_currency

text

amount/currency from budget object

planning_budget_projectid

text

projectID from budget object

documents_count

bigint

Number of documents in documents array

documenttype_counts

jsonb

JSONB object with the keys as unique documentTypes and the values as count of the appearances of that documentType in the documents array

milestones_count

bigint

Count of milestones

milestonetype_counts

jsonb

JSONB object with the keys as unique milestoneTypes and the values as a count of the appearances of that milestoneType in the milestones array

planning

jsonb

JSONB of planning object

field_list

jsonb

All JSON paths in the planning object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

planning_documents_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

document_index

bigint

Position of the document in the documents array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

document

jsonb

JSONB of the document

documenttype

text

documentType field from the document object

format

text

format field from the document object

field_list

jsonb

All JSON paths in the document object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

planning_milestones_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

milestone_index

bigint

Position of the milestone in the milestone array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

milestone

jsonb

JSONB of milestone object

type

text

type from milestone object

code

text

code from milestone object

status

text

status from milestone object

field_list

jsonb

All JSON paths in the milestone object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

Tender section

tender_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

tender_id

text

id from tender object

tender_title

text

title from tender object

tender_status

text

status from tender object

tender_description

text

description from tender object

tender_value_amount

numeric

amount from value object

tender_value_currency

text

currency from value object

tender_minvalue_amount

numeric

amount from minValue object

tender_minvalue_currency

text

currency from minValue object

tender_procurementmethod

text

procumentMethod form tender object

tender_mainprocurementcategory

text

mainProcurementCategory from tender object

tender_additionalprocurementcategories

jsonb

additionalProcurementCategories from tender object

tender_awardcriteria

text

awardCriteria from tender object

tender_submissionmethod

text

submissionMethod from tender object

tender_tenderperiod_startdate

timestamp

startDate from tenderPeriod object

tender_tenderperiod_enddate

timestamp

endDate from tenderPeriod object

tender_tenderperiod_maxextentdate

timestamp

maxExtentDate from tenderPeriod object

tender_tenderperiod_durationindays

numeric

durationInDays from tenderPeriod object

tender_enquiryperiod_startdate

timestamp

startDate from enquiryPeriod object

tender_enquiryperiod_enddate

timestamp

endDate from enquiryPeriod object

tender_enquiryperiod_maxextentdate

timestamp

maxExtentDate from enquiryPeriod object

tender_enquiryperiod_durationindays

numeric

durationInDays from enquiryPeriod object

tender_hasenquiries

text

hasEnquiries from tender object

tender_eligibilitycriteria

text

`eligibilityCriteria from tender object

tender_awardperiod_startdate

timestamp

startDate from awardPeriod object

tender_awardperiod_enddate

timestamp

endDate from awardPeriod object

tender_awardperiod_maxextentdate

timestamp

maxExtentDate from awardPeriod object

tender_awardperiod_durationindays

numeric

durationInDays from awardPeriod object

tender_contractperiod_startdate

timestamp

startDate from awardPeriod object

tender_contractperiod_enddate

timestamp

endDate from awardPeriod object

tender_contractperiod_maxextentdate

timestamp

maxExtentDate from awardPeriod object

tender_contractperiod_durationindays

numeric

durationInDays from awardPeriod object

tender_numberoftenderers

numeric

numberOfTenderers from tender object

tenderers_count

integer

Count of amount of tenderers

documents_count

bigint

Count of amount of tender documents

documenttype_counts

jsonb

JSONB object with the keys as unique documentTypes and the values as count of the appearances of that documentType in the documents array

milestones_count

bigint

Count of milestones

milestonetype_counts

jsonb

JSONB object with the keys as unique milestoneTypes and the values as a count of the appearances of that milestoneType in the milestones array

items_count

bigint

Count of items

tender

jsonb

JSONB of tender object

field_list

jsonb

All JSON paths in the tender object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

tender_summary_no_data

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

tender_id

text

id from tender object

tender_title

text

title from tender object

tender_status

text

status from tender object

tender_description

text

description from tender object

tender_value_amount

numeric

amount from value object

tender_value_currency

text

currency from value object

tender_minvalue_amount

numeric

amount from minValue object

tender_minvalue_currency

text

currency from minValue object

tender_procurementmethod

text

procumentMethod form tender object

tender_mainprocurementcategory

text

mainProcurementCategory from tender object

tender_additionalprocurementcategories

jsonb

additionalProcurementCategories from tender object

tender_awardcriteria

text

awardCriteria from tender object

tender_submissionmethod

text

submissionMethod from tender object

tender_tenderperiod_startdate

timestamp

startDate from tenderPeriod object

tender_tenderperiod_enddate

timestamp

endDate from tenderPeriod object

tender_tenderperiod_maxextentdate

timestamp

maxExtentDate from tenderPeriod object

tender_tenderperiod_durationindays

numeric

durationInDays from tenderPeriod object

tender_enquiryperiod_startdate

timestamp

startDate from enquiryPeriod object

tender_enquiryperiod_enddate

timestamp

endDate from enquiryPeriod object

tender_enquiryperiod_maxextentdate

timestamp

maxExtentDate from enquiryPeriod object

tender_enquiryperiod_durationindays

numeric

durationInDays from enquiryPeriod object

tender_hasenquiries

text

hasEnquiries from tender object

tender_eligibilitycriteria

text

`eligibilityCriteria from tender object

tender_awardperiod_startdate

timestamp

startDate from awardPeriod object

tender_awardperiod_enddate

timestamp

endDate from awardPeriod object

tender_awardperiod_maxextentdate

timestamp

maxExtentDate from awardPeriod object

tender_awardperiod_durationindays

numeric

durationInDays from awardPeriod object

tender_contractperiod_startdate

timestamp

startDate from awardPeriod object

tender_contractperiod_enddate

timestamp

endDate from awardPeriod object

tender_contractperiod_maxextentdate

timestamp

maxExtentDate from awardPeriod object

tender_contractperiod_durationindays

numeric

durationInDays from awardPeriod object

tender_numberoftenderers

numeric

numberOfTenderers from tender object

tenderers_count

integer

Count of amount of tenderers

documents_count

bigint

Count of amount of tender documents

documenttype_counts

jsonb

JSONB object with the keys as unique documentTypes and the values as count of the appearances of that documentType in the documents array

milestones_count

bigint

Count of milestones

milestonetype_counts

jsonb

JSONB object with the keys as unique milestoneTypes and the values as a count of the appearances of that milestoneType in the milestones array

items_count

bigint

Count of items

tender_items_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

item_index

bigint

Position of the item in the items array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

item

jsonb

JSONB of item object

item_id

text

id field in the item object

quantity

numeric

quantity from the item object

unit_amount

numeric

amount from the unit/value object

unit_currency

text

currency from the unit/value object

item_classification

text

Concatenation of classification/scheme and classification/id

item_additionalidentifiers_ids

jsonb

JSONB list of the concatenation of additionalClassification/scheme and additionalClassification/id

additional_classification_count

integer

Count of additional classifications

field_list

jsonb

All JSON paths in the item object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

tender_documents_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

document_index

bigint

Position of the document in the documents array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

document

jsonb

JSONB of the document

documenttype

text

documentType field from the document object

format

text

format field from the document object

field_list

jsonb

All JSON paths in the document object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

tender_milestones_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

milestone_index

bigint

Position of the milestone in the milestone array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

milestone

jsonb

JSONB of milestone object

type

text

type from milestone object

code

text

code from milestone object

status

text

status from milestone object

field_list

jsonb

All JSON paths in the milestone object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

Awards section

awards_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

award_index

bigint

Position of the award in the awards array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

award_id

text

id field from award object

award_title

text

title field from award object

award_status

text

status field from award object

award_description

text

description field from award object

award_value_amount

numeric

value field from award/amount object

award_value_currency

text

currency field from award/amount object

award_date

timestamp

date field from award object

award_contractperiod_startdate

timestamp

startDate field from contractPeriod

award_contractperiod_enddate

timestamp

endDate field from contractPeriod

award_contractperiod_maxextentdate

timestamp

maxExtentDate field from contractPeriod

award_contractperiod_durationindays

numeric

durationInDays field from contractPeriod

suppliers_count

integer

The number of suppliers declared for this award.

documents_count

bigint

Number of documents in documents array

documenttype_counts

jsonb

JSONB object with the keys as unique documentTypes and the values as count of the appearances of that documentType in the documents array

items_count

bigint

Count of items

award

jsonb

JSONB of award object

field_list

jsonb

All JSON paths in the award object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

award_suppliers_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

award_index

bigint

Position of the award in the awards array

supplier_index

bigint

Position of the supplier in the supplier array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

supplier

jsonb

JSONB of supplier object

supplier_parties_id

text

id from supplier object

supplier_identifier

text

Concatenation of scheme and id from identifier object in the form <scheme>-<id>

unique_identifier_attempt

text

The id from party object if it exists, otherwise the identifier described above if it exists, otherwise the party name

supplier_additionalidentifiers_ids

jsonb

JSONB list of the concatenation of scheme and id of all additionalIdentifier objects

supplier_additionalidentifiers_count

integer

Count of additional identifiers

link_to_parties

integer

Does this buyer link to a party in the parties array using the id field from buyer object linking to the id field in a party object? If this is true then 1, otherwise 0

link_with_role

integer

If there is a link does the parties object have suppliers in its roles list? If it does then 1 otherwise 0

party_index

bigint

Position of the party in the parties array

field_list

jsonb

All JSON paths in the supplier object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

award_items_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

award_index

bigint

Position of the award in the awards array

item_index

bigint

Position of the item in the items array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

item

jsonb

JSONB of item object

item_id

text

id field in the item object

quantity

numeric

quantity from the item object

unit_amount

numeric

amount from the unit/value object

unit_currency

text

currency from the unit/value object

item_classification

text

Concatenation of classification/scheme and classification/id

item_additionalidentifiers_ids

jsonb

JSONB list of the concatenation of additionalClassification/scheme and additionalClassification/id

additional_classification_count

integer

Count of additional classifications

field_list

jsonb

All JSON paths in the item object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

award_documents_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

award_index

bigint

Position of the award in the awards array

document_index

bigint

Position of the document in the documents array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

document

jsonb

JSONB of the document

documenttype

text

documentType field from the document object

format

text

format field from the document object

field_list

jsonb

All JSON paths in the document object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

Contracts section

contracts_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

contract_index

bigint

Position of the contract in the contracts array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

award_id

text

awardID field in contract object

link_to_awards

integer

If there is an award with the above awardID then 1 otherwise 0

contract_id

text

id field from contract object

contract_title

text

title field from contract object

contract_status

text

status field from contract object

contract_description

text

description field from contract object

contract_value_amount

numeric

amount field from value object

contract_value_currency

text

currency field from value object

datesigned

timestamp

dateSigned from contract object

contract_period_startdate

timestamp

startDate field from contractPeriod

contract_period_enddate

timestamp

endDate field from contractPeriod

contract_period_maxextentdate

timestamp

maxExtentDate field from contractPeriod

contract_period_durationindays

numeric

durationInDays field from contractPeriod

documents_count

bigint

Number of documents in documents array

documenttype_counts

jsonb

JSONB object with the keys as unique documentTypes and the values as count of the appearances of that documentType in the documents array

milestones_count

bigint

Count of milestones

milestonetype_counts

jsonb

JSONB object with the keys as unique milestoneTypes and the values as a count of the appearances of that milestoneType in the milestones array

items_count

bigint

Count of items

implementation_documents_count

bigint

Number of documents in documents array

implementation_documenttype_counts

jsonb

JSONB object with the keys as unique documentTypes and the values as count of the appearances of that documentType in the documents array

implementation_milestones_count

bigint

Number of documents in documents array

implementation_milestonetype_counts

jsonb

JSONB object with the keys as unique milestoneTypes and the values as count of the appearances of that milestoneType in the milestone array

contract

jsonb

JSONB of contract object

field_list

jsonb

All JSON paths in the contract object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

contract_items_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

contract_index

bigint

Position of the contract in the contracts array

item_index

bigint

Position of the item in the items array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

item

jsonb

JSONB of item object

item_id

text

id field in the item object

quantity

numeric

quantity from the item object

unit_amount

numeric

amount from the unit/value object

unit_currency

text

currency from the unit/value object

item_classification

text

Concatenation of classification/scheme and classification/id

item_additionalidentifiers_ids

jsonb

JSONB list of the concatenation of additionalClassification/scheme and additionalClassification/id

additional_classification_count

integer

Count of additional classifications

field_list

jsonb

All JSON paths in the item object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

contract_documents_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

contract_index

bigint

Position of the contract in the contracts array

document_index

bigint

Position of the document in the documents array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

document

jsonb

JSONB of the document

documenttype

text

documentType field from the document object

format

text

format field from the document object

field_list

jsonb

All JSON paths in the document object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

contract_milestones_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

contract_index

bigint

Position of the contract in the contracts array

milestone_index

bigint

Position of the milestone in the milestone array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

milestone

jsonb

JSONB of milestone object

type

text

type from milestone object

code

text

code from milestone object

status

text

status from milestone object

field_list

jsonb

All JSON paths in the milestone object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

Contract implementation section

contract_implementation_transactions_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

contract_index

bigint

Position of the contract in the contracts array

transaction_index

bigint

Position of the transaction in the transaction array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

transaction_amount

numeric

amount field from the value object or the deprecated amount object

transaction_currency

text

currency field from the value object or the deprecated amount object

transaction

jsonb

JSONB of transaction object

field_list

jsonb

All JSON paths in the transaction object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

contract_implementation_documents_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

contract_index

bigint

Position of the contract in the contracts array

document_index

bigint

Position of the document in the documents array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

document

jsonb

JSONB of the document

documenttype

text

documentType field from the document object

format

text

format field from the document object

field_list

jsonb

All JSON paths in the document object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.

contract_implementation_milestones_summary

Column Name

Data Type

Description

id

bigint

Unique id representing a release, compiled_release or record

contract_index

bigint

Position of the contract in the contracts array

milestone_index

bigint

Position of the milestone in the milestone array

release_type

text

Either release, record, compiled_release or embedded_release. With “release”, individual releases are read through the release table. With “record”, a compiled release is read from a record’s compiledRelease field through the record table. With “compiled_release”, a compiled release is read through the compiled_release table, which is calculated by Kingfisher Process (for example, by merging a collection of releases). With “embedded_releases”, individual releases are read from a record’s releases array through the record table.

collection_id

integer

id from Kingfisher collection table

ocid

text

ocid from the data

release_id

text

Release id from the data. Relevant for releases and not for compiled_releases or records

data_id

integer

id for the “data” table in Kingfisher that holds the original JSON data.

milestone

jsonb

JSONB of milestone object

type

text

type from milestone object

code

text

code from milestone object

status

text

status from milestone object

field_list

jsonb

All JSON paths in the milestone object, excluding array indices, expressed as a JSONB object in which keys are paths and values are NULL. This column is only available if the –field-lists option was used.