Welcome to wal2json Discussions!

[eulerto]

👋 Welcome!

We’re using Discussions as a place to connect with other members of our community. We hope that you:

• Ask questions you’re wondering about.
• Share ideas.
• Engage with other community members.
• Welcome others and are open-minded. Remember that this is a community we
  build together 💪.

[honne23]

Heyo! been using wal2json to spin up my own real-time database and I'm loving it so far. I was wondering if there was support for array types?

I'm pipelining wal2json to a kafka broker and I noticed that an empty array appears to be returned as a string {}.
Would it make more sense to return array structures as []?

Looking into the returned columntypes array, wal2json can tell that the column is an array type (uuid[]) as can be seen below.
Example:

Table:

CREATE TABLE IF NOT EXISTS messages (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL,
  chat_id UUID NOT NULL,
  message_timestamp TIMESTAMP NOT NULL,
  users_read UUID[] NOT NULL,
  message_text VARCHAR NOT NULL,
  FOREIGN KEY (user_id) REFERENCES xuser(id),
  FOREIGN KEY (user_id,chat_id) REFERENCES user_chats(user_id,chat_id)
);

Example insert statement:

WITH result AS (INSERT INTO messages (user_id, chat_id, message_timestamp, users_read, message_text) 
VALUES ($1, $2, NOW(), '{}', $3) RETURNING chat_id, id)
UPDATE chats SET last_message=result.id FROM result WHERE chats.id=result.chat_id;

Extract of JSON output (for an insert):

\"columntypes\":[\"uuid\",\"uuid\",\"uuid\",\"timestamp without time zone\",\"uuid[]\",\"character varying\"],

\"columnvalues\":[\"a636d8b7-9574-4e7c-8c50-f14e24d47fd0\",\"9c926b9b-138c-49e4-ad67-d1a9c5820974\",\"2f1c1dfb-c3b2-4139-8502-92d5638b0cb1\",\"2021-01-02 16:18:25.845373\",\"{}\",\"🤔\"]},

I didn't want to open an issue as I couldn't tell if I could just pass in a parameter to enable the datatype or not!

[honne23]

In the meantime I've found a work-around for those of us using golang courtesy of adharris

var (
	// unquoted array values must not contain: (" , \ { } whitespace NULL)
	// and must be at least one char
	unquotedChar  = `[^",\\{}\s(NULL)]`
	unquotedValue = fmt.Sprintf("(%s)+", unquotedChar)

	// quoted array values are surrounded by double quotes, can be any
	// character except " or \, which must be backslash escaped:
	quotedChar  = `[^"\\]|\\"|\\\\`
	quotedValue = fmt.Sprintf("\"(%s)*\"", quotedChar)

	// an array value may be either quoted or unquoted:
	arrayValue = fmt.Sprintf("(?P<value>(%s|%s))", unquotedValue, quotedValue)

	// Array values are separated with a comma IF there is more than one value:
	arrayExp = regexp.MustCompile(fmt.Sprintf("((%s)(,)?)", arrayValue))

	valueIndex int
)

// Find the index of the 'value' named expression
func init() {
	for i, subexp := range arrayExp.SubexpNames() {
		if subexp == "value" {
			valueIndex = i
			break
		}
	}
}

// Parse the output string from the array type.
// Regex used: (((?P<value>(([^",\\{}\s(NULL)])+|"([^"\\]|\\"|\\\\)*")))(,)?)
func parseArray(array string) []string {
	results := make([]string, 0)
	matches := arrayExp.FindAllStringSubmatch(array, -1)
	for _, match := range matches {
		s := match[valueIndex]
		// the string _might_ be wrapped in quotes, so trim them:
		s = strings.Trim(s, "\"")
		results = append(results, s)
	}
	return results
}

[eulerto]

wal2json does not expand a Postgres array into a JSON array. The output is always a string as the Postgres array output format.