Get the value of the first item of JSON object I know that the first element of an object is kind of a nonsense, but cannot find a different title. If you find a better name I'll be happy to change!

I use pg_memento to log a schema. Some tables in my schema have a stats column (it's business logic, nothing to do with SQL). So here is my request:

SELECT changes, stmt_date::date FROM pgmemento.table_event_log el
  JOIN pgmemento.row_log r ON (r.event_id=el.id)
  JOIN pgmemento.transaction_log t on (el.transaction_id=t.txid)
WHERE r.changes ?&amp; (SELECT array_agg(key) 
                    FROM pgmemento.row_log, 
                    LATERAL jsonb_each_text(changes) 
                    WHERE key like '%_stat' 
                    GROUP BY key)
ORDER BY stmt_date::date


Ok great so this what I get:

changes, stmt_date
{"X_stat": "INCOMING"}, 2017-12-13
{"W_stat": "CREATED"}, 2017-12-13


What I would like is grouping "INCOMING", "CREATED", etc. I'm sure this is doable with a CTE, but cannot find a what to get the element of the first key.

What I would like is

type, date, count
INCOMING, 2017-12-13, 1
CREATED, 2017-12-13, 1


The tricks is to count *_stat by by date :-/

EDIT:

Almost there :

WITH status_per_table as (
SELECT changes, stmt_date::date FROM pgmemento.table_event_log el
  JOIN pgmemento.row_log r ON (r.event_id=el.id)
  JOIN pgmemento.transaction_log t on (el.transaction_id=t.txid)
WHERE r.changes ?&amp; (SELECT array_agg(DISTINCT key) FROM pgmemento.row_log, LATERAL jsonb_each_text(changes) WHERE key like '%_stat' GROUP BY key)
ORDER BY stmt_date::date) 
SELECT value from status_per_table, LATERAL jsonb_each_text(changes)


The problem is SELECT array_agg(DISTINCT key) FROM pgmemento.row_log, LATERAL jsonb_each_text(changes) WHERE key like '%_stat' GROUP BY key return multiple value
OK so the final query is :

WITH status_per_table AS (
SELECT
  changes,
  stmt_date :: DATE
FROM pgmemento.table_event_log el
  JOIN pgmemento.row_log r ON (r.event_id = el.id)
  JOIN pgmemento.transaction_log t ON (el.transaction_id = t.txid)
WHERE r.changes ?| (SELECT array((SELECT DISTINCT key
                                  FROM pgmemento.row_log, LATERAL jsonb_each_text(changes)
                                  WHERE key LIKE '%_stat'
                                  GROUP BY key)))
) SELECT
    value,
    stmt_date,
    count(value)
  FROM status_per_table, LATERAL jsonb_each_text(changes)
  GROUP BY value, stmt_date
  ORDER BY stmt_date DESC


Don't hesitate to post a  suggestion from a new post name...