From: Patrick <cto@satcharters.com>
To: pgsql-admin@lists.postgresql.org
Cc: sbob@quadratum-braccas.com
Subject: Re: Parse / print all elements of a json data column -
Date: Fri, 20 Dec 2019 11:44:55 -0600
Message-ID: <20191220114455.56998c30@pnk5.int.kreppshouse.com> (raw)
In-Reply-To: <1e83d780-ece1-9f27-05f8-5c6db7ce04a8@quadratum-braccas.com>
References: <1e83d780-ece1-9f27-05f8-5c6db7ce04a8@quadratum-braccas.com>
On Thu, 12 Dec 2019 13:02:42 -0700
"S.Bob" <sbob@quadratum-braccas.com> wrote:
> 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
Hi. are you by chance looking for something like this?:
$ cat get_jsonb.sql
CREATE TABLE json_test
(
data JSONB NOT NULL
);
INSERT INTO json_test
(
data
)
VALUES
(
'{
"change": [
{
"columnnames": [
"id",
"company_name",
"status",
"active_date"
],
"columntypes": [
"integer",
"character varying(100)",
"character varying(10)",
"timestamp with time zone"
],
"columnvalues": [
1,
"Acme CO",
"B+",
"2017-12-12 10:14:39.899462-05"
],
"kind": "insert",
"schema": "lr_test_schema",
"table": "lr_test_tab"
}
],
"timestamp": "2019-12-12 10:14:39.901252-05"
}'
);
-- SELECT jsonb_each(data)
-- FROM json_test;
-- SELECT jsonb_object_keys(data)
-- FROM json_test;
SELECT jsonb_each(data -> 'change' -> 0) AS the_key_value_pairs
FROM json_test
UNION ALL
SELECT row('timestamp', data -> 'timestamp')
FROM json_test;;
DROP TABLE json_test;
$ psql -U testy -h localhost -d postgres
Password for user testy:
psql (9.4.25)
Type "help" for help.
postgres=> SELECT version();
version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.25 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit (1 row)
postgres=> \i get_jsonb.sql
CREATE TABLE
INSERT 0 1
the_key_value_pairs
--------------------------------------------------------------------------------------------------------------------
(kind,"""insert""")
(table,"""lr_test_tab""")
(schema,"""lr_test_schema""")
(columnnames,"[""id"", ""company_name"", ""status"", ""active_date""]")
(columntypes,"[""integer"", ""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""")
(7 rows)
DROP TABLE
\q
Probably not exactly what you are asking for, but it might be a step in
the right direction? CTEs or subqueries might be your friend here too.
Regards,
Patrick
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: cto@satcharters.com, pgsql-admin@lists.postgresql.org, sbob@quadratum-braccas.com
Subject: Re: Parse / print all elements of a json data column -
In-Reply-To: <20191220114455.56998c30@pnk5.int.kreppshouse.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox