PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Unable to convert jsonb into row

I want the jsonb output in row format for each user. If the user has 2 subjects then 2 rows should be created for that user. In some JSON certidate would be present. Where certidate is not present createdOn should be used as certificate_date in output.

My table looks like below:-

create table rewards
(
id serial primary key,
userid varchar(50),
details jsonb
);

Details column in table has below records. Below are 3 jsons for 3 different user. Record looks like 1, Nik, JSON1valuefrombelow , 2, SAM, JSON2valuefrombelow and same for 3rd user

JSON1

[
  {
    "score": 80,
    "createdOn": 1664494754585,
    "subject": "Math"
  }  
]

JSON2

[
  {
    "score": 70,
    "certidate": 1664494754515,
    "subject": "Science"
  }  
]

JSON3

[
  {
    "score": 85,
    "certidate": 1664494754250,
    "subject": "Science"
    "score2": 85,
    "certidate2": 1664494754250,
    "subject2": "Math"
  }  
]

Output should appear in below format:-

id | userid | subject | certificate_date

I understand that probably jsonb_array_elements can solve problem but I am even not able to see any rows for below query:-

SELECT *
FROM rewards r, 
jsonb_array_elements(r.details->'createdOn') as createdOn;
Tags:

2

Answers


  1. 0

    All jsons looks like 1 element table, so you can change json format (if this is not real table but always one object inside) or unwind table using jsonb_array_elements

    Assuming that you want to keep table inside json jsonb_array_elements function returns each table element (and in this case this is also jsonb object).

    select 
  r.id, 
  r.userid, 
  elem.value->>'subject' as subject,
  coalesce(elem.value->'certidate',elem.value->'createdOn') as certificate_date
from rewards r, jsonb_array_elements(r.details) as elem

    To get value from first element of a table you can use

    select details->0->'createdOn' from rewards r;
    Login or Signup to reply.
  2. 0

    You need to pass r.details (the array value) to jsonb_array_elements, not r.details->'createdOn' (which doesn’t exist). It seems you are looking for

    SELECT
  id,
  userid,
  details->>'subject' AS subject,
  to_timestamp(COALESCE(details->'certidate', details->'createdOn')::float/1000) AS certificate_date
FROM rewards r, 
jsonb_array_elements(r.details) as details;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search