Postgresql jsonb querys

Create table with pgjson

CREATE TABLE asu_clients_activity_jsonb (
  id serial primary key,
  pg_timestamp    timestamp default NULL,
  data jsonb
);

Select from records distink by last timestamp

SELECT DISTINCT ON (client_id)
        pg_timestamp AS "@timestamp",
        data->>'client_id' AS client_id,
        data->'client.env'->>'asu_hosts_ssl' AS asu_hosts_ssl,
        data->>'asu_version' AS asu_version,
        data->'asu_clients_activity'->>'companies' AS companies,
        data->'asu_clients_activity'->>'users_count' AS users_count,
        data->'asu_clients_activity'->>'area_of_​​houses_thousand(sqm)' AS area_of_houses_thousand,
        data->>'global_auth_token' AS global_auth_token
FROM asu_clients_activity_jsonb
ORDER BY data->>'client_id', to_date(pg_timestamp::TEXT, 'YYYY-MM-DD') DESC;

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

 

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.