Скачать или смотреть How to Convert Comma Separated Non-JSON Strings to JSON in PostgreSQL

Learn how to easily convert comma-separated strings into JSON format in PostgreSQL with simple SQL commands and regex techniques.
---
This video is based on the question https://stackoverflow.com/q/73613571/ asked by the user 'Mano' ( https://stackoverflow.com/u/5944884/ ) and on the answer https://stackoverflow.com/a/73614316/ provided by the user 'Pepe N O' ( https://stackoverflow.com/u/15062361/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Convert comma separated non json string to json

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Convert Comma Separated Non-JSON Strings to JSON in PostgreSQL

In the world of data management, especially when using SQL databases like PostgreSQL, you may encounter situations where you need to convert a non-standard string format into a structured JSON format. This need often arises in reporting or data transformation tasks. For example, consider a text column containing a string in the format 'A:10000000,B:50000000,C:1000000,D:10000000,E:10000000'. The challenge lies in transforming this string into a proper JSON format: {"A": 10000000, "B": 50000000, "C": 1000000, "D": 10000000, "E": 10000000}.

In this guide, we’ll explore how to accomplish this conversion using PostgreSQL. We will break down the solution into manageable steps, making it easy to understand and implement.

Understanding the Problem

You have a text string that features key-value pairs, separated by commas, and you want to transform it into a JSON object. The initial format includes:

Keys (like A, B, C, D, E) followed by a colon and their respective integer values.

The pairs are separated by commas.

Your goal is to convert this format into a JSON-structured object, which requires encapsulating keys in double quotes and ensuring that the values are assigned correctly in JSON format.

The Solution

Step 1: Basic Conversion for Single Capital Letter Keys

If all your keys are single capital letters, you can use the regexp_replace function within a SQL query. This function allows you to search for patterns and replace them accordingly.

Here’s the command you can use:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

concat('{', ..., '}') wraps the result in curly braces to form a valid JSON object.

regexp_replace(...) searches for each capital letter and replaces it with the letter enclosed in quotes using " to maintain HTML safety.

The final ::json converts the string into a JSON data type for PostgreSQL.

Step 2: General Case with Multiple Letter Keys

If your key strings may contain multiple letters, both uppercase and lowercase (like 'Ac', 'BT', 'Cs'), you can modify the above query slightly to accommodate more extensive patterns:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

The pattern ([a-zA-Z]+) captures one or more letters, allowing for keys consisting of more than one character.

The rest of the query follows the same logic as before.

Conclusion

Converting a comma-separated non-JSON string to a structured JSON format in PostgreSQL is straightforward with the use of string manipulation functions like concat and regexp_replace. By understanding the patterns in your data and using the correct SQL functions, you can easily transform your data into a usable format for applications requiring JSON.

Utilize the snippets provided above to streamline your data conversions, and you’ll find working with JSON in PostgreSQL much more manageable. Happy querying!