psql jsonb array within a array

Question

My data structure is like array within array. I have a 2 column table named id (int), meta (JSONB) where data is stored like :

id : meta
12 : [... ]

[...] shown below:

[
  {
    "task": "T3",
    "task_label": "what is wrong.",
    "value": "Something's wrong"
  },
  {
    "task": "T0",
    "task_label": "What's wrong about this image?",
    "value": [
      {
        "x": 228.52696228027344,
        "y": 42.95765686035156,
        "tool": 0,
        "frame": 0,
        "width": 738.8717193603516,
        "height": 45.10553741455078,
        "details": [],
        "tool_label": "Sender"
      },
      {
        "x": 1302.4683837890625,
        "y": 642.2169799804688,
        "tool": 2,
        "frame": 0,
        "width": 423.1329345703125,
        "height": 115.98565673828125,
        "details": [],
        "tool_label": "Action"
      }
    ]
  }
]

I want to run sql query to find the list of all tool_label where task = "T0".

The result should be like

id: meta->task->value->tool_label
12: Sender, Action

GMB · Accepted Answer · 2020-06-17 22:28:06Z

1

You can use jsonb_array_elements() twice, then filter and aggregate:

select t.id, string_agg(l2.obj ->> 'tool_label', ',') tool_labels
from mytable t
cross join lateral jsonb_array_elements(t.meta)            as l1(obj)
cross join lateral jsonb_array_elements(l1.obj -> 'value') as l2(obj)
where l1.obj ->> 'task' = 'T0'
group by t.id

edited Jun 17, 2020 at 22:28

answered Jun 16, 2020 at 22:47

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vinod K. Ahuja Over a year ago

thanks it works great. Only modification I needed in this code was i have to put value feild in quotes ` "value" `

GMB Over a year ago

@VinodK.Ahuja: indeed, that was a typo... Fixed

Collectives™ on Stack Overflow

psql jsonb array within a array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related