0

Is it possible to get array/object number values.

I have a table called tableA:

create table "tableA" (
"_id" serial,
"userId" integer,
"dependentData" jsonb);


INSERT INTO "tableA"
("_id", "userId", "dependentData")
VALUES('55555', '1191', '[{"_id": 133, "type": "radio", "title": "questionTest7", "question": "questionTest7", "response": {"value": ["option_11"]}, "dependentQuestionResponse": [{"_id": 278, "type": "text", "title": "questionTest8", "question": "questionTest8", "response": {"value": ["street no 140"]}, "dependentQuestionResponse": []}]}, {"_id": 154, "type": "dropdown", "title": "questionTest8", "question": "questionTest8", "response": {"value": ["option_14"]}, "dependentQuestionResponse": []}]');

Array number is to be fetched. Output should be require below.

_id userId array/object
55555 1191 [0,0,1]

enter image description here

sahil
  • 109
  • 8

1 Answers1

0

You can try something like this

select id, user_id,
  (
    with
  base as (
    select o1, oo1.a oo1 from (
      select jsonb_array_elements(t.a) o1
        from (select depend_data as a) t
    ) o
    left join lateral (select a from jsonb_array_elements(o.o1-> 'dependentQuestionResponse') a) oo1 on true
  )
select json_agg(nn) from (
  select dense_rank() over(order by b.o1) - 1 nn, b.o1 from base b
  union all 
  select dense_rank() over(order by b.o1) - 1 nn, b.oo1 from base b where oo1 is not null
  order by nn
) tz
  ) as array_object
from
(select 55555 as id,
        1191 as user_id,
        '[{"_id": 133, "type": "radio", "title": "questionTest7", "question": "questionTest7", "response": {"value": ["option_11"]}, "dependentQuestionResponse": [
        {"_id": 278, "type": "text", "title": "questionTest8", "question": "questionTest8", "response": {"value": ["street no 140"]},"dependentQuestionResponse": []}]},
        {"_id": 154, "type": "dropdown", "title": "questionTest8", "question": "questionTest8", "response": {"value": ["option_14"]}, "dependentQuestionResponse": []}]'::jsonb as depend_data) t
Andrew
  • 175
  • 7