Date: Fri, 20 Dec 2019 11:44:55 -0600
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 -
Message-ID: <20191220114455.56998c30@pnk5.int.kreppshouse.com>
In-Reply-To: <1e83d780-ece1-9f27-05f8-5c6db7ce04a8@quadratum-braccas.com>
References: <1e83d780-ece1-9f27-05f8-5c6db7ce04a8@quadratum-braccas.com>
Organization: IT
User-Agent: Nothing from (microsoft|redhat), because security, simplicity,
 and standards compliance really are important
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

On Thu, 12 Dec 2019 13:02:42 -0700
"S.Bob" <sbob@quadratum-braccas.com> wrote:

> All;
>=20
>=20
> I've setup a logical replication slot in a 9.6 cluster.
>=20
> I have pulled data with a query like this:
>=20
> SELECT * FROM pg_logical_slot_get_changes('lr_cdc_slot', NULL, NULL,=20
> 'pretty-print', '1')
>=20
>=20
> I've even staged the returned 'data' column in a table like this:
>=20
> create table json_test as select data::jsonb from=20
> pg_logical_slot_get_changes('lr_cdc_slot', null,=20
> null,'include-timestamp','1');
>=20
>=20
> 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.
>=20
>=20
> Here's some of the queries that do work but none of them give me a
> full breakout of the fields:
>=20
>=20
> select jsonb_each(data) from json_test ;
>=20
>  =C2=A0(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""]}]")
>  =C2=A0(timestamp,"""2019-12-12 10:14:39.901252-05""")
> (2 rows)
>=20
>=20
>=20
> select jsonb_object_keys(data::jsonb) from json_test ;
>  =C2=A0jsonb_object_keys
> -------------------
>  =C2=A0change
>  =C2=A0timestamp
> (2 rows)
>=20
>=20
>=20
> How can I pull each field and it's value from this (i.e kind =3D
> insert, table =3D lr_test_tab, columnnames =3D ...)?
>=20
>=20
> 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=3D> SELECT version();
                                                    version                =
                                    =20
---------------------------------------------------------------------------=
-------------------------------------
 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=3D> \i get_jsonb.sql
CREATE TABLE
INSERT 0 1
                                                the_key_value_pairs        =
                                        =20
---------------------------------------------------------------------------=
-----------------------------------------
 (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