Parse / print all elements of a json data column -

From: "S(dot)Bob" <sbob(at)quadratum-braccas(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Parse / print all elements of a json data column -
Date: 2019-12-12 20:02:42
Message-ID: 1e83d780-ece1-9f27-05f8-5c6db7ce04a8@quadratum-braccas.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

All;

I've setup a logical replication slot in a 9.6 cluster.

I have pulled data with a query like this:

SELECT * FROM pg_logical_slot_get_changes('lr_cdc_slot', NULL, NULL,
'pretty-print', '1')

I've even staged the returned 'data' column in a table like this:

create table json_test as select data::jsonb from
pg_logical_slot_get_changes('lr_cdc_slot', null,
null,'include-timestamp','1');

I want to isolate the various "fields" and "values" of the output json
string (i.e. the table name, the operation, the columns, etc) However I
am not having much luck.

Here's some of the queries that do work but none of them give me a full
breakout of the fields:

select jsonb_each(data) from json_test ;

 (change,"[{""kind"": ""insert"", ""table"": ""lr_test_tab"", ""
schema"": ""lr_test_schema"", ""columnnames"": [""id"", ""compan
y_name"", ""status"", ""active_date""], ""columntypes"": [""inte
ger"", ""character varying(100)"", ""character varying(10)"", ""
timestamp with time zone""], ""columnvalues"": [1, ""Acme CO"",
""B+"", ""2017-12-12 10:14:39.899462-05""]}]")
 (timestamp,"""2019-12-12 10:14:39.901252-05""")
(2 rows)

select jsonb_object_keys(data::jsonb) from json_test ;
 jsonb_object_keys
-------------------
 change
 timestamp
(2 rows)

How can I pull each field and it's value from this (i.e kind = insert,
table = lr_test_tab, columnnames = ...)?

Thanks in advance

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Min Wang 2019-12-12 20:26:35 PostgreSQL 11 sends shared secret to RADIUS server in all low case
Previous Message Laurenz Albe 2019-12-12 14:08:16 Re: Insert hex / octal literals in a query?