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

Question

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: [email protected]\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": "[email protected]", "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!

Peskoff

now can jsonb contain not json?.. maybe it is text column?.. – Misdeed

column type is jsonb, but data in that column is YAML string format. – Peskoff

I'm facing same issue, hopefully somebody can answer this. – Violent

Why do you have YAML string in JSONB column? – Hamza

"I ran PostgreSQL 9.5.25 locally, and I can't insert the YAML data into a JSONB column. Output: ERROR: invalid input syntax for type json LINE 3: VALUES(3, '---\nemployee:\n reference: ''6''\n first_name: T... ^ DETAIL: Token "-" is invalid. CONTEXT: JSON data, line 1: -... – Meraree