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

Postgresql – Postgres: Transform JSON records

I have a function returning jsonb records

SELECT get_my_records( 'some', 'args' );
{ "a": 1, "b": 3.14 }
{ "a": 2, "b": 2.71 }
...

and trying to use jsonb_to_record() to convert the records to

a | b
--+-----
1 | 3.14
2 | 2.71
...

Tried

SELECT * FROM jsonb_to_record( val ) as x( a int, b double precision ) FROM get_my_records( 'some', 'arg' ) AS val;

and a few variations but without success. How does one go about it?

(probably lacking proper terminology to get meaningful results from the web search)

Tags:

2

Answers


  1. 0

    Here how to do it using jsonb_to_record() :

    SELECT rec.*
FROM get_my_records( 'some', 'arg' ) AS val,
     jsonb_to_record(val) as rec(a int, b numeric );

    Or simply using the operator ->> :

    SELECT val->>'a' a , val->>'b' b
FROM get_my_records( 'some', 'arg' ) AS val
    Login or Signup to reply.
  2. 0

    You were mostly correct in what you did, but you added multiple FROM lists: fiddle

    create function get_my_records(a1 text, a2 text) returns setof jsonb language sql as $f$
select * from (values('{ "a": 1, "b": 3.14 }'::jsonb)
                    ,('{ "a": 2, "b": 2.71 }'::jsonb)) as a(b) $f$;

select (j->>'a')::int as a, 
       (j->>'b')::numeric as b 
from get_my_records( 'some', 'args' ) as jdata(j);
    a b
    1 3.14
    2 2.71 
    --You can't have multiple `from` lists
SELECT * 
FROM jsonb_to_record( val ) as x( a int, b double precision ) 
FROM get_my_records( 'some', 'arg' ) AS val;

    ERROR:  syntax error at or near "FROM"
LINE 4: FROM get_my_records( 'some', 'arg' ) AS val;
        ^

    --You can extend your `from` list by joining
SELECT x.* 
FROM get_my_records( 'some', 'arg' ) AS val 
LEFT JOIN LATERAL jsonb_to_record( val ) as x( a int, b double precision ) ON TRUE;
    a b
    1 3.14
    2 2.71 
    --You can join implicitly, since `lateral` is implied by dependence between sources
--and on true just lets them join freely
SELECT x.* 
FROM get_my_records( 'some', 'arg' ) AS val,
     jsonb_to_record( val ) as x( a int, b double precision );
    a b
    1 3.14
    2 2.71
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search