pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedParse / print all elements of a json data column -
2+ messages / 2 participants
[nested] [flat]
* Parse / print all elements of a json data column -
@ 2019-12-12 20:02 S.Bob <sbob@quadratum-braccas.com>
2019-12-20 17:44 ` Re: Parse / print all elements of a json data column - Patrick <cto@satcharters.com>
0 siblings, 1 reply; 2+ messages in thread
From: S.Bob @ 2019-12-12 20:02 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Parse / print all elements of a json data column -
2019-12-12 20:02 Parse / print all elements of a json data column - S.Bob <sbob@quadratum-braccas.com>
@ 2019-12-20 17:44 ` Patrick <cto@satcharters.com>
0 siblings, 0 replies; 2+ messages in thread
From: Patrick @ 2019-12-20 17:44 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; +Cc: sbob@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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-12-20 17:44 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-12 20:02 Parse / print all elements of a json data column - S.Bob <sbob@quadratum-braccas.com>
2019-12-20 17:44 ` Patrick <cto@satcharters.com>
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