Menu
Is there a straightforward way to put entire row from one Postgres table into a JSON column in another table?
Here's an example to illustrate what I'm looking to do. Let's say I've got a table people, with name, age, and data columns:
column type
-----------
name text
age int
data json
I'd like to merge in my table of 2012_customers, which has a lot more columns. How would I stuff all those extra columns into the JSON column of people, preserving the column names as the data keys? Here's some pseudo-SQL for that:
insert into people
select
name,
age,
all_fields_as_json() as json
from customers_2012
916
to_json() function Any SQL value can be converted to JSON using this PostgreSQL JSON function.
Postgres is a relational database that allows you to combine relational and non-relational data effortlessly, giving users/applications flexibility in accessing and handling the data. Additionally, Postgres includes native support for querying and processing JSON data.
2) Querying PostgreSQL JSON Data To query data, you can use a SELECT statement like in any other SQL query. You can use the native PostgreSQL operators to query the data in PostgreSQL. The operator -> returns a JSON object field by key. The operator ->> returns a JSON object field by text.
To query JSON data, you can use standard T-SQL. If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).
In this situation, the correct syntax is to use row_to_json(table_name):
insert into people
select
name,
age,
row_to_json(customers_2012) as json
from customers_2012
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With