-1

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

Mohammed zuhair
  • 105
  • 1
  • 10
  • 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 Oct 15 '20 at 09: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. –  Oct 15 '20 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](https://stackoverflow.com/questions/45243186/how-to-cast-json-array-to-text-array) for a solution –  Oct 15 '20 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 Oct 15 '20 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 –  Oct 15 '20 at 10:35
  • I am passing a json object to my stored procedure which contains name of user, email, phone, and skill set. i am following RDBMS which will store the data in three different tables 1) user table contains user_id, user_name, user_phone. 2) skill table contain skill_id, skill_name. 3) user_skill table contain id, user_id, usr_skill_id. skill table i.e table 2 should not contain the duplicate values. challenge is i am not able to cast the json array to text array for skill_set .. my json object {"Name": "abhish mathew", "phone_number": "", "Skills": ["J2EE", "JDBC", "Html","mapping"]} – Mohammed zuhair Oct 15 '20 at 10:44
  • Thank you Mr. @a_horse_with_no_name for helping.. your effort made my day – Mohammed zuhair Oct 15 '20 at 12:33

1 Answers1

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$
Mohammed zuhair
  • 105
  • 1
  • 10