'I want to Itrate JSON and convert into rows from postgresql
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|