Re: How to use Array in Array of Json

--000000000000f2dcb5059839618a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Dear Friends,
Thanks for your email. But here I am sharing my actual use case.


-- Function: public.sample_json_array(json)

-- DROP FUNCTION public.sample_json_array(json);

CREATE OR REPLACE FUNCTION public.sample_json_array(vin_ip_param json)
  RETURNS void AS
$BODY$
Declare
v_text text;
arr integer[];
cnt integer :=3D 1;
val varchar;
BEGIN
/*
SELECT sample_json_array(' {
"vin_loc_cd" : [1, 2, 3],
"vin_comp_cd" : [5, 6, 7]
}
   ');
*/
DROP TABLE IF EXISTS my_loc ;

raise notice 'ABC %', (SELECT d.value FROM json_each_text(vin_ip_param) AS
d WHERE d.key=3D'vin_loc_cd' );

CREATE TEMP TABLE my_loc AS
(SELECT * from epps_admin.epps_location_mst lm
WHERE
 *lm.loc_cd In *
* (SELECT d.value FROM json_each_text(vin_ip_param) AS d WHERE
d.key=3D'vin_loc_cd' )*
* ); *

END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.sample_json_array(json)
  OWNER TO epps_programmer;

*I WANT TO USE array variable "vin_loc_cd"  from input vin_ip_param json in
my sql query.*

Please provide solution for this scenario.



On Mon, Nov 25, 2019 at 9:44 PM <[email protected]> wrote:

> Hi Monalee,
>
>
>
> You can just use a json/jsonb variable. Content should be looking like
> this:
>
>
>
> [
>
> {=E2=80=9Cvin_comp_cd=E2=80=9D: 1, =E2=80=9Cvin_div_cd=E2=80=9D:11, =E2=
=80=9Cvin_array=E2=80=9D: [{=E2=80=9Cvin_loc_cd=E2=80=9D: 33,
> =E2=80=9Cvin_bt_cd=E2=80=9D: 44}] },
>
> =E2=80=A6
>
> {=E2=80=9Cvin_comp_cd=E2=80=9D: N, =E2=80=9Cvin_div_cd=E2=80=9D:NN, =E2=
=80=9Cvin_array=E2=80=9D: [{=E2=80=9Cvin_loc_cd=E2=80=9D: AA,
> =E2=80=9Cvin_bt_cd=E2=80=9D: BB}] }
>
>
>
> ]
>
>
>
> Or if =E2=80=9Cvin_loc_cd=E2=80=9D and =E2=80=9Cvin_bt_cd=E2=80=9D can be=
 independent arrays if they are
> independent.
>
>
>
> Best,
>
> Igor
>
>
>
> *From:* Monalee Bhandge <[email protected]>
> *Sent:* Monday, November 25, 2019 4:02 AM
> *To:* [email protected]
> *Subject:* How to use Array in Array of Json
>
>
>
> Dear Friends,
>
> My function definition is as:
>
>
>
> CREATE OR REPLACE FUNCTION schema1.samplefun1(
>
>     vin_comp_cd smallint,
>
>     vin_div_cd smallint,
>
>     vin_loc_cd smallint[],
>
>     vin_bt_cd integer[],
>
>   )
>
>
>
> Here instead of passing parameter I want to just send a single i/p in
> Json[].
>
>
>
> My problem is how to pass array in Json[] variable. And how to use in cod=
e.
>
>
>
>
>
> Thanking You!
>
> Monalee
>
> Database Lead Engineer.
>

--000000000000f2dcb5059839618a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div>Dear Friends,</div>=
<div dir=3D"ltr">Thanks for your=C2=A0email. But here I am sharing my actua=
l use case.<div><br></div><div><br></div><div><div>-- Function: public.samp=
le_json_array(json)</div><div><br></div><div>-- DROP FUNCTION public.sample=
_json_array(json);</div><div><br></div><div>CREATE OR REPLACE FUNCTION publ=
ic.sample_json_array(vin_ip_param json)</div><div>=C2=A0 RETURNS void AS</d=
iv><div>$BODY$</div><div>Declare=C2=A0</div><div><span style=3D"white-space=
:pre">	</span>v_text text;</div><div><span style=3D"white-space:pre">	</spa=
n>arr integer[];</div><div><span style=3D"white-space:pre">	</span>cnt inte=
ger :=3D 1;</div><div><span style=3D"white-space:pre">	</span>val varchar;<=
/div><div>BEGIN</div><div>/*</div><div>SELECT sample_json_array(&#39;<span =
style=3D"white-space:pre">		</span>{</div><div><span style=3D"white-space:p=
re">							</span>&quot;vin_loc_cd&quot; : [1, 2, 3],</div><div><span style=
=3D"white-space:pre">							</span>&quot;vin_comp_cd&quot; : [5, 6, 7]</div=
><div><span style=3D"white-space:pre">						</span>}</div><div><span style=
=3D"white-space:pre">					</span> =C2=A0 =C2=A0&#39;);</div><div>*/</div><d=
iv>DROP TABLE IF EXISTS my_loc ;</div><div><br></div><div>raise notice &#39=
;ABC %&#39;, (SELECT d.value FROM json_each_text(vin_ip_param) AS d WHERE d=
.key=3D&#39;vin_loc_cd&#39; );</div><div><br></div><div>CREATE TEMP TABLE m=
y_loc AS</div><div>(SELECT * from epps_admin.epps_location_mst lm</div><div=
>WHERE=C2=A0</div><div>=C2=A0<b style=3D"background-color:rgb(255,255,0)">l=
m.loc_cd In=C2=A0</b></div><div><b style=3D"background-color:rgb(255,255,0)=
">=C2=A0(SELECT d.value FROM json_each_text(vin_ip_param) AS d WHERE d.key=
=3D&#39;vin_loc_cd&#39; )</b></div><div><b style=3D"background-color:rgb(25=
5,255,0)">=C2=A0);=C2=A0</b></div><div><br></div><div>END;</div><div><br></=
div><div>$BODY$</div><div>=C2=A0 LANGUAGE plpgsql VOLATILE</div><div>=C2=A0=
 COST 100;</div><div>ALTER FUNCTION public.sample_json_array(json)</div><di=
v>=C2=A0 OWNER TO epps_programmer;</div></div><div><br></div><div><b>I WANT=
 TO USE array variable &quot;vin_loc_cd&quot; =C2=A0from input vin_ip_param=
 json in my sql query.</b></div><div><b><br></b></div><div><span style=3D"b=
ackground-color:rgb(255,153,0)">Please provide solution for this scenario.<=
/span></div><div><br></div><div><br></div></div></div></div></div><br><div =
class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Mon, Nov 25,=
 2019 at 9:44 PM &lt;<a href=3D"mailto:[email protected]">2.andriychuk=
@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><div lang=3D"EN-US"><div class=3D"gmail-m_3721883698099055040Wor=
dSection1"><p class=3D"MsoNormal">Hi Monalee,<u></u><u></u></p><p class=3D"=
MsoNormal"><u></u>=C2=A0<u></u></p><p class=3D"MsoNormal">You can just use =
a json/jsonb variable. Content should be looking like this:<u></u><u></u></=
p><p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p><p class=3D"MsoNormal">[<u=
></u><u></u></p><p class=3D"MsoNormal" style=3D"text-indent:0.5in">{=E2=80=
=9Cvin_comp_cd=E2=80=9D: 1, =E2=80=9Cvin_div_cd=E2=80=9D:11, =E2=80=9Cvin_a=
rray=E2=80=9D: [{=E2=80=9Cvin_loc_cd=E2=80=9D: 33, =E2=80=9Cvin_bt_cd=E2=80=
=9D: 44}] },<u></u><u></u></p><p class=3D"MsoNormal" style=3D"text-indent:0=
.5in">=E2=80=A6<u></u><u></u></p><p class=3D"MsoNormal" style=3D"text-inden=
t:0.5in">{=E2=80=9Cvin_comp_cd=E2=80=9D: N, =E2=80=9Cvin_div_cd=E2=80=9D:NN=
, =E2=80=9Cvin_array=E2=80=9D: [{=E2=80=9Cvin_loc_cd=E2=80=9D: AA, =E2=80=
=9Cvin_bt_cd=E2=80=9D: BB}] }<u></u><u></u></p><p class=3D"MsoNormal" style=
=3D"text-indent:0.5in"><u></u>=C2=A0<u></u></p><p class=3D"MsoNormal">]<u><=
/u><u></u></p><p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p><p class=3D"Ms=
oNormal">Or if =E2=80=9Cvin_loc_cd=E2=80=9D and =E2=80=9Cvin_bt_cd=E2=80=9D=
 can be independent arrays if they are independent.<u></u><u></u></p><p cla=
ss=3D"MsoNormal"><u></u>=C2=A0<u></u></p><p class=3D"MsoNormal">Best,<u></u=
><u></u></p><p class=3D"MsoNormal">Igor<u></u><u></u></p><p class=3D"MsoNor=
mal"><u></u>=C2=A0<u></u></p><p class=3D"MsoNormal"><b>From:</b> Monalee Bh=
andge &lt;<a href=3D"mailto:[email protected]" target=3D"_blank"=
>[email protected]</a>&gt; <br><b>Sent:</b> Monday, November 25,=
 2019 4:02 AM<br><b>To:</b> <a href=3D"mailto:[email protected]=
g" target=3D"_blank">[email protected]</a><br><b>Subject:</b> =
How to use Array in Array of Json<u></u><u></u></p><p class=3D"MsoNormal"><=
u></u>=C2=A0<u></u></p><div><div><p class=3D"MsoNormal">Dear Friends,<u></u=
><u></u></p><div><p class=3D"MsoNormal">My function definition=C2=A0is as:<=
u></u><u></u></p></div><div><p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p>=
</div><div><div><p class=3D"MsoNormal">CREATE OR REPLACE FUNCTION schema1.s=
amplefun1(<u></u><u></u></p></div><div><p class=3D"MsoNormal">=C2=A0 =C2=A0=
 vin_comp_cd smallint,<u></u><u></u></p></div><div><p class=3D"MsoNormal">=
=C2=A0 =C2=A0 vin_div_cd smallint,<u></u><u></u></p></div><div><p class=3D"=
MsoNormal">=C2=A0 =C2=A0 vin_loc_cd smallint[],<u></u><u></u></p></div><div=
><p class=3D"MsoNormal">=C2=A0 =C2=A0 vin_bt_cd integer[],<u></u><u></u></p=
></div><div><p class=3D"MsoNormal">=C2=A0 )<u></u><u></u></p></div><div><p =
class=3D"MsoNormal"><u></u>=C2=A0<u></u></p></div><div><p class=3D"MsoNorma=
l">Here instead of passing parameter=C2=A0I want to just send a single i/p =
in Json[].<u></u><u></u></p></div></div><div><p class=3D"MsoNormal"><u></u>=
=C2=A0<u></u></p></div><div><p class=3D"MsoNormal">My problem is how to pas=
s array in Json[] variable. And how to use in code.<u></u><u></u></p></div>=
<div><p class=3D"MsoNormal"><u></u>=C2=A0<u></u></p></div><div><p class=3D"=
MsoNormal"><u></u>=C2=A0<u></u></p></div><div><p class=3D"MsoNormal">Thanki=
ng You!<u></u><u></u></p></div><div><p class=3D"MsoNormal">Monalee<u></u><u=
></u></p></div><div><p class=3D"MsoNormal">Database Lead Engineer.<u></u><u=
></u></p></div></div></div></div></div></blockquote></div>

--000000000000f2dcb5059839618a--