Is the vertica has the function that converts row to JSON format.?

edited March 2021 in General Discussion

Is the vertica has the function that converts row to JSON format.?

  • The table is not a flex table*
    example)
    select * from table;
    ** general result**
    id text
    1 amy
    2 john

if i use the json function
select json(*) from table;
** json result **

result
{'id':1, 'text':'amy'}
{'id':2, 'text':'john'}

Answers

  • SergeBSergeB Employee

    You can use the vmap functions to build a vmap for each row and then get its JSON representation.

     select maptoString(mapput(emptymap(),id,"text" using parameters keys=SetMapKeys('id','text'))) as json_string from foo;
                  json_string
    ---------------------------------------
     {
        "id": "1",
          "text": "amy"
    }
     {
        "id": "2",
        "text": "john"
    }
    
  • Oh.. Thank you so much.. @SergeB
    I have one more question..
    If i need all column,, then, i have to write all column in the function??

  • SergeBSergeB Employee

    @HyeontaeJu Yes, you will need to list all the columns you wish to include in your JSON result.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file