I can not extract data from a JSON type column in PostgreSQL

postgres query json array of objects
postgres json example
postgres json array contains
postgres json functions
postgres json type
postgres string to json
postgres json vs jsonb
postgresql query json_array_elements

I want to extract data from a json type column to insert them into a table in order to normalize a database.

The JSON type column is called "info" and an example of a record is the following:

[ { "major" : "International business", 
    "end" : "2007", 
    "name" : "Annamalai University",
    "degree" : "Master Degree", 
    "start" : "2005", "desc" : "" 
  }, 
  { "major" : "Mechanical Engineering", 
    "end" : "1990", 
    "name" : "Bharathidasan University", 
    "degree" : "Bachelor Degree", 
    "start" : "1990", "desc" : "" 
  }
]

This is my code:

SELECT id,
       (json_array_elements(info)->>'education')::json ->> 'key' AS key1 
FROM perfiles
WHERE id = 1252710;

This is the result I want to obtain: table result example

How should I do the query?

Thanks in advance

use json_to_recordset

    SELECT  x.*
FROM pjson_table
   , json_to_recordset(myjson::json) x 
        (  major text
         , "end" text
         , name text
         , degree text
         , start text
         ,"desc" text
        )

demo link

 major                  end      name                   degree         start
International business  2007    Annamalai University    Master Degree   2005
Mechanical Engineering  1990    Bharathidasan University    Bachelor Degree 1990

Documentation: 9.3: JSON Functions and Operators, text, Get JSON object field as text, '{"a":1,"b":2}'::json->>'b' If the data type is not built in, and there is a cast from the type to json, the cast function will be used to� There is a simple way to extract values from JSON data into their own column. First, create the new column with the appropriate data type (e.g., VARCHAR): ALTER TABLE user ADD COLUMN country VARCHAR; Then run an update statement over your whole table to populate the new column: UPDATE user SET country = (rawData->>'Country')::VARCHAR WHERE ID >= 0;

Try something like this

select *
from (
  select
  json_to_recordset(info) as ed(major text, end int, name text, degree text, start int, desc text)
  from perfiles 
  where id = 1252710
)

Ref: https://www.postgresql.org/docs/9.5/functions-json.html

Documentation: 9.6: JSON Types, Such data can also be stored as text, but the JSON data types have the By contrast, jsonb does not preserve white space, does not preserve We store these documents in a table named api, in a jsonb column named jdoc. Wave 1: PostgreSQL 9.2 (2012) added support for JSON data type. JSON database in 9.2 was fairly limited (and probably overhyped at that point) – basically a glorified string with some JSON validation thrown in. It is useful to validate incoming JSON and store in the database. More details are provided below.

You may use cross join lateral with json_array_elements and list the elements in the select

SELECT  p.id, 
           j->>'major'::text AS major, 
           (j->>'end')::int  AS "end", 
           j->>'name'        AS NAME, 
           j->>'degree'      AS degree, 
           j->>'start'       AS start, 
           j->>'desc'        AS "desc" 
FROM  perfiles p 
CROSS JOIN LATERAL json_array_elements(info) AS j

Or use json_to_recordset by specifying column list in the FROM clause

select p.id,
j.* FROM   perfiles p
  cross join lateral json_to_recordset(info) 
as j(major text, "end" int, name text, degree text, start int, "desc" text);

Demo

How to Query a JSON Column in PostgreSQL, Write queries, visualize data, and share your results. Add a Default Value to a Column � How to Remove a Default Value From a Column � How to Add a Not Null Here's how you can query your JSON column in PostgreSQL: preferences.beta is true (boolean) -- This requires type casting preferences->' beta' from json to� PostgreSQL has two native data types to store JSON documents: JSON and JSONB. The key difference between them is that JSON stores data in a raw format and JSONB stores data in a custom binary format. Our focus here is going to be on the JSONB data type because it allows the contents to be indexed and queried with ease.

PostgreSQL JSON Tutorial, Let's get started by creating a new table for practicing with JSON data type. CREATE TABLE orders ( id serial NOT NULL PRIMARY KEY, info json NOT NULL ); To insert data into a JSON column, you have to ensure that data is in a valid� The info column stores the data in the form of JSON. Insert JSON data. To insert data into a JSON column, you have to ensure that data is in a valid JSON format. The following INSERT statement inserts a new row into the orders table.

How to use the JSON Datatype in PostgreSQL: A , Learn how to use PostgreSQL's great functionality to work with JSON objects, including Return type is json and the result cannot be used with functions and operators that Note that in the example the casting of the column to datatype json is performed. Looking at an extract from the entry for London, we have {… json: Returns the value as JSON. If the data type is not built in, and there is a cast from the type to json, the cast function will be used to perform the conversion. Otherwise, for any value other than a number, a Boolean, or a null value, the text representation will be used, escaped and quoted so that it is legal JSON.

Can I find all JSON scalars in PostgreSQL 9.3?, Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, Trying to look up a key in a json scalar returns null. So I'm not sure if this function will work on 9.3, and it won't on 9.4. However, unlike the JSONB column, JSON columns store JSON in its original� Wave 1: PostgreSQL 9.2 (2012) added support for JSON data type JSON database in 9.2 was fairly limited (and probably overhyped at that point) - basically a glorified string with some JSON

Comments
  • Thanks! It Works!
  • Thanks the 3 answers were very Useful! Thank you!