Running into problem while trying to generate json using recursive cte
My structure contains id and parent_id on the same table. The id is tie to the parent_id, and I won't know how deep the structure would go.
Here's the table and code I have created https://dbfiddle.uk/DFCYOzUm
I did a fair amount of research and this is the closest solution I have found:
Recursively generate JSON tree from hierarchical table in Postgres and jOOQ
this is what I am getting
{
"id": "1",
"children": [
{
"id": "1.1",
"children": [
{
"id": "1.1.2",
"children": [
],
"unit_name": "C",
"randominfo": "C"
}
],
"unit_name": "C",
"randominfo": "C"
}
],
"unit_name": "A",
"randominfo": "A"
},
{
"id": "1",
"children": [
{
"id": "1.1",
"children": [
{
"id": "1.1.1",
"children": [
{
"id": "1.1.1.1",
"children": [
{
"id": "1.1.1.1.1",
"children": [
],
"unit_name": "C",
"randominfo": "C"
This the structure I am looking for, I want 1.1.2 and 1.1.1 to be under 1.1 :
{
"id": "1",
"children": [
{
{
"id": "1.1",
"children": [
{
"id": "1.1.1",
"children": [
{
"id": "1.1.1.1"
"children": [}
},
{
"id": "1.1.1.2"
"children": [}
}
],
},
{
id": "1.1.2",
"children" []
}
],
}
}
]
}```
Any help would be appreciated!
1.2if thisidis not in your data?