Home PostgreSQL select query to parse YAML string to JSON for JSONB type column
Reply: 0

PostgreSQL select query to parse YAML string to JSON for JSONB type column

MayankJ
1#
MayankJ Published in 2017-12-05 13:19:42Z

I am using PostgreSQL 9.5.10 on the Ubuntu machine. I need to write a SQL query to select data from JSONB column. But JSONB column contains the data in YAML format as string not in JSON format.

Database JSONB field return below data:

"---\nemployee:\n reference: '6'\n first_name: Test\n last_name: Test\n email: test@test.test.edu\n new_title: Manager in Training\n former_title: N/A\n annual_salary: 5000000\n new_salary_grade: '10'\n annual_salary_currency: USD\n former_salary_grade: N/A\norigin_address:\n country_alpha3: USA\n country_name: USA\n"

if we have JSON data from JSONB column like:

{"employee": {"reference": "6", "first_name": "Test", "last_name": "Test", "email": "test@test.test.edu", "new_title": "Manager in Training", "former_title": "N/A", "annual_salary": 5000000, "new_salary_grade": "10", "annual_salary_currency": "USD", "former_salary_grade": "N/A"}, "origin_address": {"country_alpha3": "USA", "country_name": "USA"}}

then we can write SQL query on JSONB column like:

select test_field->'employee'->'first_name' as name from employees;

But this query is not working on JSONB column when the column returns the YAML string.

I didn't find any predefined function or regular expression to parse YAML string to JSON format in a SQL query.

Please help me to make the SQL query on JSONB column for YAML string format data.

Thanks!

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.325146 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO