How to use JSON array in JOIN?-CodePudding

I have got follow data base schema:

CREATE TABLE "public"."regions" ( 
    "id" Integer DEFAULT nextval('regions_id_seq'::regclass) NOT NULL,
    "ru_name" Character Varying( 100 ),
    "translite_name" Character Varying( 100 ),
    PRIMARY KEY ( "id" ) );
 ;


CREATE TABLE "public"."tokens" ( 
    "user_id" BigInt NOT NULL,
    "token_start_date" Timestamp Without Time Zone NOT NULL,
    "token_end_date" Timestamp Without Time Zone NOT NULL,
    "regions" JSON,
    "id" Character( 1 ) NOT NULL,
    "access_token" Character Varying( 50 ) NOT NULL,
    PRIMARY KEY ( "id" ) );
 ;

CREATE TABLE "public"."users" ( 
    "id" BigInt DEFAULT nextval('user_id_sequence'::regclass) NOT NULL,
    "registration_date" Timestamp Without Time Zone NOT NULL,
    "name" Character Varying( 50 ) NOT NULL,
    "lastname" Character Varying NOT NULL,
    "email" Character Varying NOT NULL,
    PRIMARY KEY ( "id" ),
    CONSTRAINT "unique_users_email" UNIQUE( "email" ) );
 ;

I need to display for every user date of expiring token and regions names for this token. Something like:

user_id token_end_date region_as_json_object
5 "2021-12-27" [{id: 1, ru_name:"Region1"}, {id: 2, ru_name:"Region2"}]

I wrote next code:

SELECT 
  ru_name 
FROM "tokens" 
JOIN regions ON regions.id = ANY (json_array_elements_text(regions) )
WHERE user_id = 5 ;

But it does not work.

sample data:

--
-- PostgreSQL database dump
--

-- Dumped from database version 13.1
-- Dumped by pg_dump version 13.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: regions; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.regions (
    id integer NOT NULL,
    ru_name character varying(100),
    fz223_name character varying(100),
    fz44_name character varying(100),
    translite_name character varying(100)
);


ALTER TABLE public.regions OWNER TO postgres;

--
-- Name: regions_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.regions_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.regions_id_seq OWNER TO postgres;

--
-- Name: regions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.regions_id_seq OWNED BY public.regions.id;


--
-- Name: tokens; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.tokens (
    id character(1) NOT NULL,
    user_id bigint NOT NULL,
    token_start_date timestamp without time zone NOT NULL,
    token_end_date timestamp without time zone NOT NULL,
    access_token character varying(50) NOT NULL,
    regions json
);


ALTER TABLE public.tokens OWNER TO postgres;

--
-- Name: users; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.users (
    id bigint DEFAULT nextval('public.user_id_sequence'::regclass) NOT NULL,
    name character varying(50) NOT NULL,
    lastname character varying NOT NULL,
    email character varying NOT NULL,
    registration_date timestamp without time zone NOT NULL
);


ALTER TABLE public.users OWNER TO postgres;

--
-- Name: regions id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.regions ALTER COLUMN id SET DEFAULT nextval('public.regions_id_seq'::regclass);


--
-- Data for Name: regions; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.regions VALUES
    (1, 'Адыгейская республика', 'Adygeya_Resp', 'Adygeja_Resp', 'Adygejskaya_respublika'),
    (2, 'Алтайский край', 'Altayskii__krai', 'Altajskij_kraj', 'Altajskij_kraj'),
    (3, 'Алтайская республика', 'Altay_Resp', 'Altaj_Resp', 'Altajskaya_respublika'),
    (4, 'Амурская область', 'Amurskaya_obl', 'Amurskaja_obl', 'Amurskaya_oblast'),
    (5, 'Архангельская область', 'Arhangelskaya_obl', 'Arkhangelskaja_obl', 'Arhangelskaya_oblast'),
    (6, 'Астраханская область', 'Astrahanskaya_obl', 'Astrakhanskaja_obl', 'Astrahanskaya_oblast'),
    (7, 'Байконур', 'Baikonur_g', 'Bajkonur_g', 'Bajkonur'),
    (8, 'Башкортостан', 'Bashkortostan_Resp', 'Bashkortostan_Resp', 'Bashkortostan'),
    (9, 'Белгородская область', 'Belgorodskaya_obl', 'Belgorodskaja_obl', 'Belgorodskaya_oblast'),
    (10, 'Брянская область', 'Brianskaya_obl', 'Brjanskaja_obl', 'Bryanskaya_oblast'),
    (11, 'Бурятская область', 'Buryatiya_Resp', 'Burjatija_Resp', 'Buryatskaya_oblast');


--
-- Data for Name: tokens; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.tokens VALUES
    ('1', 3, '2020-03-05 00:00:00', '2020-03-08 00:00:00', 'sdfa78saaf4', NULL),
    ('2', 3, '2020-04-08 00:00:00', '2020-04-18 00:00:00', 'asasdfgkfaf4', NULL),
    ('3', 3, '2020-05-15 00:00:00', '2020-09-25 00:00:00', '934rfeasfsad', '[1,2,4]'),
    ('4', 3, '2020-05-15 00:00:00', '2020-09-25 00:00:00', '934rfeasfsad', '[4,5,7]'),
    ('5', 5, '2021-05-15 00:00:00', '2021-12-25 00:00:00', '934rfeasfsad', '[1,4,6,8]'),
    ('6', 5, '2021-06-15 00:00:00', '2022-01-25 00:00:00', '934rfeasfsad', '[12]');


--
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.users VALUES
    (3, 'Mike', 'Klinton', '[email protected]', '2010-01-02 00:00:00'),
    (5, 'Piter', 'Miklson', '[email protected]', '2011-01-02 00:00:00');


--
-- Name: regions_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.regions_id_seq', 1, false);


--
-- Name: regions regions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.regions
    ADD CONSTRAINT regions_pkey PRIMARY KEY (id);


--
-- Name: tokens tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.tokens
    ADD CONSTRAINT tokens_pkey PRIMARY KEY (id);


--
-- Name: users unique_users_email; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT unique_users_email UNIQUE (email);


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- Name: tokens userr_id_fk; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.tokens
    ADD CONSTRAINT userr_id_fk FOREIGN KEY (user_id) REFERENCES public.users(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--

CodePudding user response：

select user_id, token_end_date::date, 
       json_agg(json_build_object('id',region_id,'ru_name',ru_name)) as regions_json_object
from
(
  select user_id, token_end_date, 
         json_array_elements_text(regions)::integer as region_id 
  from tokens
) as t
join regions on t.region_id = regions.id
group by user_id, token_end_date;

CodePudding user response：

Try this :

SELECT 
  ru_name 
FROM 
( SELECT json_array_elements_text(regions) AS r
    FROM public.tokens
   WHERE user_id = 5
) AS r
JOIN public.regions
ON regions.id = r :: integer ;

see the result in dbfiddle

CodePudding user response：

You can use CROSS JOIN and after collecting, data use JOIN for combine data.

Demo

with data as (
  select
    t.*,
    (ej.value ->> 'id')::int as region_id
  from
    "public"."tokens" t
    cross join json_array_elements(t.regions) ej
  where
    user_id = 5
)
select
  r.ru_name
from
  "public"."regions" r
  inner join data d on d.region_id = r.id