Debian Question posted in
The oficial documentation can be found here.

How to get JSON values by index number using PostgerSQL? – Debian

I have this sample data:

   ID          Name
    1     {'a':'content1'}
    1     {'b':'content1'}
    1     {'c':'content2'}
    1     {'d':'content2'}
    1     {'e':'content3'}
    1     {'f':'content3'}
    2     ['content4']
    2     ['content4']
    2     ['content5']
    2     ['content5']

with

ID : INT

Name : JSON

I want to have this result

   ID       Name
    1     content1
    1     content1
    1     content2
    1     content2
    1     content3
    1     content3
    2     content4
    2     content4
    2     content5
    2     content5

Usually, when dealing with JSON, I always use ::JSON ->> 'key'

but, when ID = 1 then key of name column change greatly. So I wonder if there is any way to get value (content) without specificly call the key of JSON values.

I literally dont know how to solve this problem to provide my try.

p/s: postgreSQL only please

VERSION: PostgreSQL 11.4 (Debian 11.4-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

Tags:

2

Answers


  1. 0

    You can use json_typeof() to determine what to use in a lateral join to either json_each_text() for object values or json_array_elements_text() for array values and then union the results.

    select o.id, e.v as name
  from j_by_index o
       cross join lateral json_each_text(o.name) as e(k,v)
 where json_typeof(o.name) = 'object'
union all
select a.id, e.v
  from j_by_index a
       cross join lateral json_array_elements_text(a.name) as e(v)
 where json_typeof(a.name) = 'array';

┌────┬──────────┐
│ id │   name   │
├────┼──────────┤
│  1 │ content1 │
│  1 │ content1 │
│  1 │ content2 │
│  1 │ content2 │
│  1 │ content3 │
│  1 │ content3 │
│  2 │ content4 │
│  2 │ content4 │
│  2 │ content5 │
│  2 │ content5 │
└────┴──────────┘
(10 rows)
    Login or Signup to reply.
  2. 0

    If you need to deal with multiple keys/array elements, you can apply a limit clause when extracting the keys/elements.

    select t.id, o.content
from test t
  cross join lateral (
    (
      select content
      from jsonb_each_text(t.name) as e(key,content)
      where jsonb_typeof(t.name) = 'object'
      limit 1
    )
    union all
    (
      select *
      from jsonb_array_elements_text(t.name)
      where jsonb_typeof(t.name) = 'array'
      limit 1
    )
  ) o ;

    Note that the limit will pick an arbitrary element, it’s not guaranteed that it’s the "first" one – but highly likely.

    To pick an element by index, you could use:

    select t.id, o.content
from test t
  cross join lateral (
    select e.content
    from jsonb_each_text(t.name) with ordinality as e(key,content,idx)
    where jsonb_typeof(t.name) = 'object'
      and e.idx = 1
    union all
    select e.element
    from jsonb_array_elements_text(t.name)  with ordinality as e(element,idx)
    where jsonb_typeof(t.name) = 'array'
     and e.idx = 1
  ) o ;

    If your name column is defined as json (rather than jsonb which it should be), then you need to use the corresponding json_xxx function (not jsonb_xxx)

    Online example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search