jsonb acess array element

0
votes

I have a jsonb value like this in my Postgres database:

{
   "data":[
      [
         {
            "id":"identity_phone_1",
            "type":"string",
            "class":"col-md-6",
            "label":"Téléphone",
            "value":"tamereenslip"
         },
         {
            "id":"identity_imei_1",
            "type":"string",
            "class":"col-md-6",
            "label":"IMEI",
            "value":"d"
         }
      ],
      [
         {
            "id":"identity_phone_2",
            "type":"string",
            "class":"col-md-6",
            "label":"Téléphone",
            "value":"e"
         },
         {
            "id":"identity_imei_2",
            "type":"string",
            "class":"col-md-6",
            "label":"IMEI",
            "value":"f"
         }
      ]
   ]
}

I need to access automatically to all of the value field. For now I find the right command but it's not automatic, I need the 0's to increment automatically, is it possible?

phones #>> '{data, 0, 0, value}'

Edit : I want to retrieve all the values in one command :

tamerenslip, d, e, f

Also, I need to put this command in a select :/

2
postgresqljsonb
What do you mean with "increment automatically". Please edit your question and add the result you expect from the query - a_horse_with_no_name
Should that be 4 different columns, or one column where the values are separated with a comma? - a_horse_with_no_name
Also: which Postgres version are you using? - a_horse_with_no_name

2 Answers

0
votes

Is this what you are trying to do?

with invar as (
select '{
   "data":[
      [
         {
            "id":"identity_phone_1",
            "type":"string",
            "class":"col-md-6",
            "label":"Téléphone",
            "value":"tamereenslip"
         },
         {
            "id":"identity_imei_1",
            "type":"string",
            "class":"col-md-6",
            "label":"IMEI",
            "value":"d"
         }
      ],
      [
         {
            "id":"identity_phone_2",
            "type":"string",
            "class":"col-md-6",
            "label":"Téléphone",
            "value":"e"
         },
         {
            "id":"identity_imei_2",
            "type":"string",
            "class":"col-md-6",
            "label":"IMEI",
            "value":"f"
         }
      ]
   ]
}'::jsonb as jdata
), getkey as (
  select jsonb_array_elements(jdata->'data') array1
    from invar
), idx1 as (
  select jsonb_array_elements(array1) array2
    from getkey
)
select array2->>'value' as value
  from idx1;

    value     
--------------
 tamereenslip
 d
 e
 f
(4 rows)

Update

with getkey as (
  select jsonb_array_elements(phones->'data') as array1
    from individu_identity
), idx1 as (
  select jsonb_array_elements(array1) array2
    from getkey
)
select array2->>'value' as value
  from idx1
 where translate(array2->>'value', ..., ...) ...
;
0
votes

If you want those values as a comma separated string, you can use this:

select ...., x.elements
from the_table t
  cross join lateral (
    select string_agg(item.element ->> 'value', ', ') elements
    from jsonb_array_elements(t.phone -> 'data') as p(items)
      cross join jsonb_array_elements(p.items) as item(element)
  ) x

Replace ... with the other columns from the table you want to display.