从JSON数组中提取键/值对

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:

我在postgres数据库中有这个JSON（B）列。我在本专栏中列出了从API获得的所有数据。现在我需要从这个数组中提取一些键/值对。对于JSON数组，如：

[{"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.

由于我是JSON的新手，对我来说，我首先需要将数组分解为对象（用大括号括起来），而且还有一些对象的postgres函数（在以下链接中给出https：//www.postgresql。 org / docs / 9.4 / static / functions-json.html）我可以用来提取所需的键/值对。

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.

运行此查询时出现以下错误。

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.

还有更好的方法来获取有需要的信息。我正在使用Java语言来显示信息。

Thanks.

谢谢。

1 个解决方案

#1

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.

如果可以的话请尝试在java中处理JSON。您可以从postgres查询完整的JSON，然后使用JSON API for Java，然后您可以解析您的JSON。