How to change JSON text of MySql database to JSON datatype of postgres sql

Question

How to change JSON text of MySql database to JSON datatype of postgres sql.I am storing JSON data in database table of Mysql and want to convert it into JSON datatype of postgres sql

Mysql create table

CREATE TABLE IF NOT EXISTS `employee` (
  `id` bigint(40) NOT NULL AUTO_INCREMENT,
  `employee_id` bigint(40) NOT NULL,
  `employee_info` text NOT NULL,
  )

In employee_info I am storing {"employee_name":"abc","address":"1","emloyee_weight":"30","age":"100","phone":"9845236775"} in MySql database which is a JSON .

and wanted to convert it into JSON datatype of postgressql

mantigatos · Accepted Answer · 2015-04-29 20:04:41Z

1

Question shouldn't left unanswered :-)

It's pretty simple:

Postgresql CREATE TABLE

CREATE TABLE employee (
  id bigserial primary key,
  employee_id bigint NOT NULL,
  employee_info json NOT NULL
);

And then you can some magic like:

select * from employee where employee_info->>'address' like '%Kr%';

or

select * from employee where CAST(employee_info->>'emloyee_weight' AS integer)>30;

Heed on postgres version, it should be 9.3 or greater

http://sqlfiddle.com/#!15/edb87/1

answered Apr 29, 2015 at 20:04

mantigatos

2961 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to change JSON text of MySql database to JSON datatype of postgres sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related