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
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? |