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

PostgreSQL: Is it possible to use the result of a subquery into a jsonb?

I have a query that gets some data, for example:

select type_of_client from table1;    

type_of_client
--------------
  restaurant
  bar
  cinema
  ...

And I would like to use these results into a jsonb element, for example:

select count(*) from table2 where fields->>'restaurant' is null;
select count(*) from table2 where fields->>'bar' is null;
select count(*) from table2 where fields->>'cinema' is null;


Expected results:
10
20
5

Is it possible?
I need to make it dynamically because there are a lot of type_of_clients at table_1

Tags:

2

Answers


  1. 0

    I’m not sure why one would do such a thing, but technically

    select (jsonb_object_agg(type_of_client, type_of_client))->>'restaurant' from table1;

    does match your example.

    There are a few useful row_to_json(b) functions, as well as other jsonb aggregations functions: https://www.postgresql.org/docs/current/functions-aggregate.html#id-1.5.8.27.5.2.4.12.1.1.1

    Login or Signup to reply.
  2. 0

    Yes, you can do that, the -> operator can take any expression of type text as its second operand, not just literals. You can use a subquery (if it returns 0 to 1 rows with a single column, not more):

    select fields->>(select type_of_client from table1) from table2;

    or you might be looking for a cross join:

    select type_of_client, field->>type_of_client from table1, table2;

    or for your use case:

    SELECT type_of_client, count(*) FILTER (WHERE field->>type_of_client IS NULL) FROM table1, table2 GROUP BY type_of_client;
SELECT type_of_client, (SELECT count(*) FROM table2 WHERE field->>type_of_client IS NULL) AS count FROM table1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search