Basically I want to itrate JSON till its length from table but rest of values of remains same till current JSON ends. My Table format is like this
| id | line | txndate | metadata | docnumber |
|---|---|---|---|---|
| 363 | [{"Id": "0", "Amount": 135000.0, "DetailType": "JournalEntryLineDetail", "Description": "Paid Office Rent Of Office", "JournalEntryLineDetail": {"AccountRef": {"name": "Rent or lease payments", "value": "57"}, "PostingType": "Debit"}}, {"Id": "1", "Amount": 135000.0, "DetailType": "JournalEntryLineDetail", "Description": "Paid Office Rent Of Office", "JournalEntryLineDetail": {"AccountRef": {"name": "Cash and cash equivalents:Bank", "value": "83"}, "PostingType": "Credit"}}] | 2021-08-16 00:00:00.000000 00:00 | {"CreateTime": "2021-08-20T05:39:38.000000Z", "LastUpdatedTime": "2021-08-20T05:39:38.000000Z"} | 332 |
| 610 | [{"Id": "0", "Amount": 4138088.25, "DetailType": "JournalEntryLineDetail", "Description": "Deposit in Bank", "JournalEntryLineDetail": {"AccountRef": {"name": "Cash and cash equivalents:Bank", "value": "83"}, "PostingType": "Debit"}}, {"Id": "1", "Amount": 4138088.25, "DetailType": "JournalEntryLineDetail", "Description": "Deposit in Bank", "JournalEntryLineDetail": {"AccountRef": {"name": "Share capital", "value": "8"}, "PostingType": "Credit"}}, {"Id": "2", "DetailType": "DescriptionOnly", "Description": "Deposit in Bank"}] | 2021-10-11 00:00:00.000000 00:00 | {"CreateTime": "2021-10-13T10:44:09.000000Z", "LastUpdatedTime": "2021-10-13T10:44:09.000000Z"} | 560 |
| 381 | [{"Id": "0", "Amount": 30000.0, "DetailType": "JournalEntryLineDetail", "Description": "Paid to Punkish", "JournalEntryLineDetail": {"AccountRef": {"name": "Advance Against Salary", "value": "103"}, "PostingType": "Debit"}}, {"Id": "1", "Amount": 30000.0, "DetailType": "JournalEntryLineDetail", "Description": "Paid to Punkish", "JournalEntryLineDetail": {"AccountRef": {"name": "Cash and cash equivalents:Bank", "value": "83"}, "PostingType": "Credit"}}] | 2021-07-01 00:00:00.000000 00:00 | {"CreateTime": "2021-08-23T05:31:42.000000Z", "LastUpdatedTime": "2021-08-23T05:47:03.000000Z"} | 521 |
But I want to extract information like following table
| id | line_id | Amount | Description | name | value | posting_type | txndate | CreatedTime | LastUpdatedTime |
|---|---|---|---|---|---|---|---|---|---|
| 363 | 0 | 13500 | Paid Office Rent Of Office | Rent or lease payments | 57 | Debit | 2021-08-16 00:00:00.000000 00:00 | 2021-08-20T05:39:38.000000Z | 2021-08-20T05:39:38.000000Z |
| 363 | 1 | 13500 | Paid Office Rent Of Office | Cash and cash equivalents:Bank | 83 | Cebit | 2021-08-16 00:00:00.000000 00:00 | 2021-08-20T05:39:38.000000Z | 2021-08-20T05:39:38.000000Z |
| 610 | 0 | 4138088.25 | Deposit in Bank | Cash and cash equivalents:Bank | 83 | Debit | 2021-10-11 00:00:00.000000 00:00 | 2021-10-13T10:44:09.000000Z | 2021-10-13T10:44:09.000000Z |
| 610 | 1 | 4138088.25 | .......... | .. | ... | ... | 2021-10-11 00:00:00.000000 00:00 | 2021-10-13T10:44:09.000000Z | 2021-10-13T10:44:09.000000Z |
| 610 | 2 | 4138088.25 | .......... | .. | ... | ... | 2021-10-11 00:00:00.000000 00:00 | 2021-10-13T10:44:09.000000Z | 2021-10-13T10:44:09.000000Z |
| 610 | 3 | 4138088.25 | .......... | .. | ... | ... | 2021-10-11 00:00:00.000000 00:00 | 2021-10-13T10:44:09.000000Z | 2021-10-13T10:44:09.000000Z |
I want to convert JSON column enteries into rows but want preserve id, txndate, CreatedTime, and LastUpdatedTime same till the length of JSON column which is line in my case.
Please guide me with solution if possible.
Note: I am using Postgresql and datatype of line column is jsonb
CodePudding user response：
Here you go, You can use json_array_elements function to convert an array of JSON to row and then query on each row
select
t.id,
e.value ->> 'Id' as line_id,
e.value ->> 'Amount' as amount,
e.value ->> 'Description' as description,
e.value -> 'JournalEntryLineDetail' -> 'AccountRef' ->> 'name' as name,
e.value -> 'JournalEntryLineDetail' -> 'AccountRef' ->> 'value' as value,
e.value -> 'JournalEntryLineDetail' ->> 'PostingType' as posting_type,
t.txndate,
t.metadata ->> 'CreateTime' as CreatedTime,
t.metadata ->> 'LastUpdatedTime' as LastUpdatedTime
from
test t
cross join json_array_elements(t.line) e