CODEWITHSUNDEEP
sqljsonpostgresql
user1858796
user1858796

Reputation: 319

Extracting key/value pairs form JSON array

I have this JSON(B) column in my postgres database. I thrown in this column all the data that I get from an API. Now i need to extract some key/value pairs from this array.e.g. for a JSON array like:

[{"myKey1": "school type", "myKey2": "primary"}, {"myKey1": "study coverage", "myKey2": "secondary"}]

i need to extract:

myKey1: school type   myKey2: primary

myKey1: study coverage  myKey22: secondary

Since i am new to JSON, to me it appears that i first need to break the array into objects (surrounded by curly brackets) and than there are some postgres functions for objects (given in the following link https://www.postgresql.org/docs/9.4/static/functions-json.html) that i can use to extract the required key/value pairs.

My database table is:

CREATE TABLE "Education" (
    "id" SERIAL NOT NULL,
    "json_text" JSONB NOT NULL,
);

I am using the following query to achieve this:

SELECT tmp_col->>'myKey1'
FROM ( 
  SELECT json_text->> 0 AS temp_col
  FROM education
) AS temp_table

I get the following error when i run this query.

Error Message

Any clues to what is missing?

Also is there any better ways to get the information in need. I am working in Java language to display the information.

Thanks.

Upvotes: 0

Views: 755

Answers (1)

Pulkit Gupta
Pulkit Gupta

Reputation: 1059

If you can then please try to process JSON in java. You can query the complete JSON from postgres and then by using JSON API for Java you can then parse your JSON.

Upvotes: 2

Related Questions