Select JSON array's fields from SQL view to create a column

Question

I have an SQL Table which one of the columns contain a JSON array in the following format:

[  
   {  
      "id":"1",
      "translation":"something here",
      "value":"value of something here"
   },
   {  
      "id":"2",
      "translation":"something else here",
      "value":"value of something else here"
   },
..
..
..
]

Is there any way to use an SQL Query and retrieve columns with the ID as header and the "value" as the value of the column? Instead of return only one column with the JSON array.

For example, if I run:

SELECT column_with_json FROM myTable

It will return the above array. Where I want to return

1,2
value of something here, value of something else here

user1498339 · Accepted Answer · 2015-03-27 10:44:52Z

0

You can't use SQL to retrieve columns from the JSON stored inside the table: to the database engine the JSON is just unstructured text saved in a text field.

Some relational databases, like PostgreSQL, have a JSON type and functions to support JSON query. If this is your case, you should be able to perform the query you want.

Check this for an example on how it work with PostgreSQL: http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/

answered Mar 27, 2015 at 10:44

user1498339

6294 silver badges9 bronze badges

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

2 Comments

Tasos Over a year ago

It is a mySQL from PHPmyADMIN in wordpress. I use a Wordpress plugin (MiwoSQL) to run SQL Queries from dashboard. Instead of doing this, will I have the option to do what I want if I use a PHP code to retrieve the files from SQL table?

user1498339 Over a year ago

Yes, you can retrieve the text using SQL and use PHP to parse the JSON to an array: stackoverflow.com/questions/11051711/parsing-sql-query-php

Collectives™ on Stack Overflow

Select JSON array's fields from SQL view to create a column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related