How to store and print array value from json object in postgresql?

Question

Here i need to extract only skill and store in 'skill_s' variable for further processing. How can i achieve this?

DO
$do$
DECLARE
    skill_s  Text[];
    jsonObject json = // Json object 
                '{
                    "Name": "bala Kala",     
                    "Education": "B.Tech", 
                    "Skills":  ["enim", "aliquip", "qui"]
                }';
BEGIN 
    SELECT jsonObject::TEXT[]->'Skills' into skill_s; 
    raise info 'JSON value Name is %', skill_s;
END
$do$

I want to print the output enim, aliquip, qui

Don't tag 3 completely different RDBMS... Only tag the RDBMS you are really using. I have removed all the conflicting tags. please retag your question correctly. — Thom A
– Thom A ♦, Commented Oct 15, 2020 at 9:51
Are you really using Postgres 9.1 and 9.3? Both are no longer maintained or supported and 9.1 didn't even have a JSON data type. — user330315
– user330315, Commented Oct 15, 2020 at 10:13
As far as I understand you question, it boils down to "How to convert a JSON array to a native text array"? See e.g. here for a solution — user330315
– user330315, Commented Oct 15, 2020 at 10:16
I tried with many things one here "SELECT json_array_elements_text(jsonObject->'Skills') into skill_s;" , but no luck. @a_horse_with_no_name. i am struggling from morning. — Mohammed zuhair
– Mohammed zuhair, Commented Oct 15, 2020 at 10:29
Did you have a look at the question I linked to? It has several solutions to convert a json(b) array into a text array. If you told us, why you want to convert it, maybe we can also give you an alternative solution that doesn't require the conversion to begin with. But without more background this is quite hard to tell — user330315
– user330315, Commented Oct 15, 2020 at 10:35

Mohammed zuhair · Accepted Answer · 2020-10-15 12:45:07Z

1

Here is the simple solution for casting json array to string array. we can achieve it using json_array_elements_text and json_array_elements. here json_array_elements returns the string within double quotes example "Python" and json_array_elements_text returns plain string. below is my code. This helped me.

DO
$do$
DECLARE
    skill_s  Text[];
    jsonObject json = // Json object 
                '{
                    "Name": "bala Kala",     
                    "Education": "B.Tech", 
                    "Skills":  ["enim", "aliquip", "qui"]
                }';
BEGIN
skill_s := array(SELECT json_array_elements_text(jsonObject->'Skills'));
    raise info 'JSON value Name is %',skill_s;
END
$do$

edited Oct 15, 2020 at 12:45

answered Oct 15, 2020 at 12:32

Mohammed zuhair

1051 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to store and print array value from json object in postgresql?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related