Skip to content

How can I create a view from a json array #3052

Answered by simolus3
RCVZ asked this question in Q&A
Discussion options

You must be logged in to vote

their keys as columns

This doesn't work if you don't know the structure of the JSON elements beforehand, as each row needs to have the same columns and the columns of a view must be inferable without actually running the query, so you can't have the columns depend on which data is in the row.
You can use json_each to use a JSON array in a FROM clause, so if you know the structure (e.g. if all the objects have foo, bar, baz fields), you can do this:

SELECT json.value->>'$.foo' foo, json.value->>'$.bar' bar, json.value->>'$.baz' baz
  FROM main_table tbl
  INNER JOIN json_each(tbl.json_column) json

You can put this into a CREATE VIEW statement to use it as a view.

Replies: 1 comment

Comment options

You must be logged in to vote
0 replies
Answer selected by RCVZ
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Category
Q&A
Labels
None yet
2 participants