This article is half-done without your Comment! *** Please share your thoughts via Comment ***
I have already written a couple of articles about the PostgreSQL JSON Data type and its different type of operations.
PostgreSQL: How to convert Table Data into JSON formatted Data?
In this post, I am going to share one more demonstration about PostgreSQL JSON.
In our system, I have stored communication message information in the form of JSON. This JSON column contains lots of different types of elements.
Now one of module required to fetch this JSON array elements into a form of STRING Array only. (Something like comma separated).
We have different type of JSON functions and operator so I have achieved this solution using jsonb_array_elements.
Below is a full demonstration of this:
Create one table with sample data:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tbl_TestJsonArray ( ID INT ,JsonData JSONB ); INSERT INTO tbl_TestJsonArray VALUES (1,'{"Name": ["Anvesh","Roy","Jenny","Neevan"]}'); |
SQL Query to convert JSON elements into String Array:
|
1 2 3 4 5 6 7 |
SELECT ID ,( SELECT string_agg(trim(JsonString::text, '"'), ', ') FROM jsonb_array_elements(JsonData->'Name') JsonString ) AS StringArray FROM tbl_TestJsonArray; |
|
1 2 3 |
id | StringArray ---------------------------------- 1 | Anvesh, Roy, Jenny, Neevan |
