How to pass value from another table to jsonb in postgres

Question

I have two tables.

Table A

 id |       json       
----+------------------
  a | {"st":[{"State": "TX", "Value":"0.02"}, {"State": "CA", "Value":"0.2" ...
----+------------------
 b  | {"st":[{"State": "TX", "Value":"0.32"}, {"State": "CA", "Value":"0.47" ...

Table B

idx |  state| dir     
----+-------+----------
  1 | TX    | 123
----+-------+----------
  2 | CA    | 15

I want to filter table A using column temp from table B. And Table B will select base upon idx value.

I want to select value from each row when state equal to temporary table which is created from tableB using where idx is certain number

lets say idx is equal to 2. That means I can create temporary table using following sql query

with tempT AS(
SELECT * 
FROM tableB 
where idx = 2);

This is what I am trying to achieve

idx |  state| value     
----+-------+----------
  2 | CA    | 0.2
----+-------+----------
  2 | CA    | 0.47

How can I do that ?

Answer 1

You should use jsonb_array_elements like:

WITH A AS
  (SELECT 'a' AS id,
          '{"st":[{"State": "TX", "Value":"0.02"}, {"State": "CA", "Value":"0.2"}]}'::jsonb AS json
   UNION SELECT 'b' AS id,
                '{"st":[{"State": "TX", "Value":"0.32"}, {"State": "CA", "Value":"0.47"}]}'::jsonb AS json),
     B AS
  (SELECT 1 AS idx,
          'TX' AS state,
          123 AS dir
   UNION SELECT 2 AS idx,
                'CA' AS state,
                15 AS dir)
SELECT *
FROM
  (SELECT A.id,
          jsonb_array_elements(A.json->'st') AS obj
   FROM A) AS A
inner JOIN B on  B.state = obj->>'State'::text
where B.idx = 2;