Get values text from column with record type

Question

I got a table jsonb table t_1, that contains column column_1 with jsonb type and i need to search in values of this elements, i know how to do this

But when i was looking for answer of how to search in jsonb values, i found a way to return a table with record type columns converted from jsonb

And i am interesting in Is there any ways to search in table that has a column with record type?

Here is a little script, that you can use, for testing situation:

    CREATE TABLE t_1 (
       ID serial NOT NULL PRIMARY KEY,
       column_1 jsonb NOT NULL
    );


    INSERT INTO t_1 (column_1)
    VALUES
    (
       '{ "01": "Lily Bush", "03": "prod2uct"}'
    ),
    (
       '{ "41": "Josh William", "12": "product7"}'
    ),
    (
       '{ "07": "Mary Clark", "items" : "product2"}'
    );

When you enter:

SELECT jsonb_each_text(column_1) FROM t_1

You wil get this table with record type column Result will be like this

"(01,"Lily Bush")"

Is there any ways to get values from such a table?

user330315 · Accepted Answer · 2019-08-29 08:08:37Z

1

Don't use set-returning functions in the SELECT list.

They return tables, so they should be used in the FROM clause. Would you write select (select * from t_1) from t_1?

select t1.id, kv.*
from t_1 t1
  cross join lateral jsonb_each_text(t1.column_1) as kv
order by t1.id;

Returns:

id | key   | value       
---+-------+-------------
 1 | 01    | Lily Bush   
 1 | 03    | prod2uct    
 2 | 12    | product7    
 2 | 41    | Josh William
 3 | 07    | Mary Clark  
 3 | items | product2

Collectives™ on Stack Overflow

Get values text from column with record type

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related