Get all jsonb values in one column for usage in to_tsvector

Question

I got a table jsonb table t_1, that contains column column_1 with jsonb type and i need to convert in to table with string, that is concatination of jsonb values

Here is a script

    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"}'
    );

I tried this:

SELECT distinct jsonb_array_elements_text(column_1 -> jsonb_object_keys(column_1)) AS Actor FROM t_1

But this return 'unable to extract elements from scalar'

I tried this:

SELECT tiket, string_agg(column_2, ', ') as list FROM( SELECT column_1 ->> jsonb_object_keys(column_1) as column_2, id as tiket FROM t_1 ) as foo1 GROUP BY tiket

but here is inner select

How can i get all jsonb values in one column without inner select, something like in first query?

I need it to use in to_tsvector

I need to use it in

setweight(
to_tsvector('simple', column_with_json::text),
'A'
)

But column_with_json::text not what i need, i need to get values, without keys

Any examples?

Don't use set returning functions (like jsonb_object_keys) in the SELECT list. Use them in the FROM part — a_horse_with_no_name
I need to use it in setweight( to_tsvector('simple', column_with_json::text), 'A' ) But column_with_json::text not what i need, i need to get values, without keys Any examples? — Bestows In Constructing

klin klin · Accepted Answer · 2019-08-29T11:18:13

Use jsonb_each_text() in a lateral join:

SELECT id, string_agg(value, ', ') AS list
FROM t_1
CROSS JOIN jsonb_each_text(column_1)
GROUP BY id

 id |          list          
----+------------------------
  1 | Lily Bush, prod2uct
  2 | product7, Josh William
  3 | Mary Clark, product2
(3 rows)

If you want to use the resulting aggregated value in a condition, use HAVING clause, e.g.:

SELECT id, string_agg(value, ', ') AS list
FROM t_1
CROSS JOIN jsonb_each_text(column_1)
GROUP BY id
HAVING string_agg(value, ', ') LIKE '%Lily%'

or a derived table and WHERE clause in a wrapper query:

SELECT *
FROM (
    SELECT id, string_agg(value, ', ') AS list
    FROM t_1
    CROSS JOIN jsonb_each_text(column_1)
    GROUP BY id
    ) s
WHERE list LIKE '%Lily%'

Both approaches are basically equivalent, in typical cases they generate the same query plan. In both the aggregation is calculated only once.

Get all jsonb values in one column for usage in to_tsvector

1 Answers