json_bm_email_basic

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

email_id

int4

√

null

name

text

2147483647

√

null

author

text

2147483647

√

null

last_modified

timestamp

29,6

√

null

sent_date

timestamp

29,6

√

null

is_template

bool

√

null

json

2147483647

√

null

Table contained 0 rows

View Definition

SELECT bm_blast.email_id,
    bm_blast.name,
    bm_blast.author,
    bm_blast.last_modified,
    bm_blast.sent_date,
    bm_blast.is_template,
    json_build_object('email_id', bm_blast.email_id, 'name', bm_blast.name, 'description', bm_blast.description, 'subject', bm_blast.subject, 'from_name', bm_blast.from_name, 'from_email', bm_blast.from_email, 'body', bm_blast.body, 'author', bm_blast.author, 'last_modified', bm_blast.last_modified, 'sent_date', bm_blast.sent_date, 'is_template', bm_blast.is_template, 'recipient_selection_query_name', ( SELECT qry_query.title
           FROM _oat.qry_query
          WHERE (qry_query.query_id = bm_blast.recipient_selection_query_id)), 'recipient_selection_parameters', ( SELECT COALESCE(json_agg(json_build_object('position', bm_recipient_selection_parameters."position", 'param_value', bm_recipient_selection_parameters.param_value, 'name', qry_parameter.name, 'description', qry_parameter.description, 'param_type', qry_parameter.param_type_id)), '[]'::json) AS "coalesce"
           FROM (_oat.bm_recipient_selection_parameters
             JOIN _oat.qry_parameter USING (query_id, "position"))
          WHERE ((bm_recipient_selection_parameters.email_id = bm_blast.email_id) AND (bm_recipient_selection_parameters.query_id = bm_blast.recipient_selection_query_id))), 'filters', ( SELECT COALESCE(json_agg(json_build_object('app_id', oat_filter.app_id, 'filter_id', oat_filter_content.filter_id, 'filter_group_id', oat_filter.filter_group_id, 'label', oat_filter.label, 'description', oat_filter.description) ORDER BY oat_filter_content.filter_id), '[]'::json) AS "coalesce"
           FROM (_oat.oat_filter_content
             JOIN _oat.oat_filter USING (app_id, filter_id))
          WHERE ((oat_filter_content.app_id = 'bm'::text) AND (oat_filter_content.id = bm_blast.email_id))), 'parent', ( SELECT json_build_object('email_id', parent.email_id, 'name', parent.name, 'is_template', parent.is_template) AS json_build_object
           FROM _oat.bm_blast parent
          WHERE (parent.email_id = bm_blast.parent_email_id)
         LIMIT 1), 'statistics', ( SELECT json_build_object('sent', count(*), 'bounced', count(NULLIF(bm_recipient_log.bounced, false)), 'known_opens', COALESCE(sum(bm_recipient_log.known_opens), (0)::bigint)) AS json
           FROM _oat.bm_recipient_log
          WHERE (bm_recipient_log.email_id = bm_blast.email_id))) AS json
   FROM _oat.bm_blast
  ORDER BY bm_blast.sent_date DESC, bm_blast.name;

Description

Columns

View Definition

Possibly Referenced Tables/Views