pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBest way to parse complex json string into table columns?
3+ messages / 2 participants
[nested] [flat]
* Best way to parse complex json string into table columns?
@ 2023-12-13 16:55 Sbob <sbob@quadratum-braccas.com>
0 siblings, 1 reply; 3+ messages in thread
From: Sbob @ 2023-12-13 16:55 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
All;
I have a client that is using a function to parse a set of rows from a
table with a json column into table columns in a materialized view.
The table is 2 columns, an id column (Primary key) and the json column
(jsonb data type)
Sample json string:
{"TXregid": "61xxx42d27xxx829g9faf414", "prices": {"MyPrice": "191.344",
"priceSource": "SAMS", "priceStatus": "P", "PriceDate": "2023-12-06",
"PriceType": "ABC ", "lastUpdatedDate":
"2022-12-07T04:39:49.664+0000"}, "descriptive": {"internal_status":
{"factor": "0.33292", "codename": "XF867", "couponType": "PCTOFF",
"factorDate": "2022-12-01T00:00:00", "factorType": "N", "currentFactor":
"0.292", "accrualDayCount": "30", "paymentDelayDays": 355,
"factoredIndicator": "true", "puttableIndicator": "false",
"mortgageAgencyCode": "93", "tradingFlatIndicator": "false",
"xDefaultIndicator": "false", "originalIssueDiscountCode": "N",
"technicalDefaultIndicator": false}, "StatusCore": {"pxiValue": "1",
"datedDate": "2012-08-01T00:00:00", "issueDate": "2012-08-01T00:00:00",
"issueType": "CST", "legalName": "My National Cash", "maturityDate":
"2092-08-01T00:00:00", "fcgtIndicator": "false", "countryOfIssue": "US",
"couponFreqDesc": "Monthly", "lastCouponDate": "2041-08-01",
"otherIssuerId": "GHT7721841", "firstCouponDate": "2011-09-25T00:00:00",
"issueDescription": "My Mortgag 3.5% AO6867 08/01/2042", "DCxIndicator":
"false", "currentCouponRate": "3.5", "issuerDescription": "My National
Mortgage", "outstandingAmount": 9139371.2105, "principalCurrency":
"USD", "couponFreqTimeUnit": "MO", "firstSettlementDate":
"2012-08-01T00:00:00", "couponFreqUnitQuantity": "1",
"paymentinKindIndicator": false, "exchangeTradedIndicator": "false"},
"assetServicing": {"XCPndicator": false, "BackendStatus": "OPEN",
"STXIndicator": "false", "DTVPIndicator": true, "RCYPIndicator":
"true"}, "classTAG": "DEBT", "classTAGname": "DEBT Level 1",
"BackendLevelCode": "SRO", "OtherLevel": "BOND22", "Factor3Code":
"TRX-VV7", "Factor3Name": "MORTGAGE2", "AccessLevel": "GENERAL",
"ActiveSystemStatus": "WAIT", "BH9Code": "PGx79S", "BH9Name": "Martin",
"BH11Code": "S9a", "BH11Name": "SOUTH", "BH12Code": "USSR", "BH12Name":
"FARM", "BusinessStatus": "ISTX"}, "identifiers": {"csstrx":
"3138LXTZ2", "usstrx": "16475266"}
The parse function adds most elements as a column, such as:
CREATE materialized view new_view_mv as
SELECT id,
(((base_table.json_col -> 'TXregid'::text))) AS
reg_id,
(((base_table.json_col -> 'descriptive'::text) ->> 'BH9Code'::text))
AS bh9_code,
(((base_table.json_col -> 'identifiers'::text) ->> 'usstrx'::text)) AS
uss_trx_code,
etc... for almost every json element
The above approach is painfully slow, is there a better performing
method of converting json strings to table columns?
Thanks in advance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best way to parse complex json string into table columns?
@ 2023-12-13 17:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sbob <sbob@quadratum-braccas.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2023-12-13 17:24 UTC (permalink / raw)
To: Sbob <sbob@quadratum-braccas.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Sbob <sbob@quadratum-braccas.com> writes:
> I have a client that is using a function to parse a set of rows from a
> table with a json column into table columns in a materialized view.
> ...
> The above approach is painfully slow, is there a better performing
> method of converting json strings to table columns?
json[b]_populate_record is meant for this sort of thing.
Also, if your source data is actually json not jsonb, that's
a seriously poor choice when you are in need of better
processing speed.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Best way to parse complex json string into table columns?
@ 2023-12-13 18:05 Sbob <sbob@quadratum-braccas.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Sbob @ 2023-12-13 18:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Thanks!
We are using jsonb
I'll have a look at
json[b]_populate_record
On 12/13/23 10:24, Tom Lane wrote:
> Sbob <sbob@quadratum-braccas.com> writes:
>> I have a client that is using a function to parse a set of rows from a
>> table with a json column into table columns in a materialized view.
>> ...
>> The above approach is painfully slow, is there a better performing
>> method of converting json strings to table columns?
> json[b]_populate_record is meant for this sort of thing.
>
> Also, if your source data is actually json not jsonb, that's
> a seriously poor choice when you are in need of better
> processing speed.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-12-13 18:05 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-13 16:55 Best way to parse complex json string into table columns? Sbob <sbob@quadratum-braccas.com>
2023-12-13 17:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-12-13 18:05 ` Sbob <sbob@quadratum-braccas.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