How to convert PostgreSQL 9.4’s jsonb type to float

by

There are two operations to get value from JSON. The first one -> will return JSON. The second one ->> will return text.

Details: JSON Functions and Operators

Try 

SELECT (json_data->'position'->>'lat')::float + 1.0 AS lat
FROM updates
LIMIT 5

More Related Contents:

  1. Operator does not exist: json = json
  2. Index for finding an element in a JSON array
  3. Query for array elements inside JSON type
  4. What’s the proper index for querying structures in arrays in Postgres jsonb?
  5. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  6. Casting NULL type when updating multiple rows
  7. Double colon `::` notation in SQL
  8. Discard millisecond part from timestamp
  9. How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?
  10. Referencing current row in FILTER clause of window function
  11. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  12. Possible to perform cross-database queries with PostgreSQL?
  13. Optimize query with OFFSET on large table
  14. GROUP BY and aggregate sequential numeric values
  15. Safely rename tables using serial primary key columns
  16. How to concatenate columns in a Postgres SELECT?
  17. How to use (install) dblink in PostgreSQL?
  18. PostgreSQL multi INSERT…RETURNING with multiple columns
  19. Postgres manually alter sequence
  20. Oracle equivalent of Postgres’ DISTINCT ON?
  21. Combine two columns and add into one new column
  22. Bulk/batch update/upsert in PostgreSQL
  23. Why is iterating through a large Django QuerySet consuming massive amounts of memory?
  24. Update a column of a table with a column of another table in PostgreSQL
  25. Check if NULL exists in Postgres array
  26. How to detect query which holds the lock in Postgres?
  27. PostgreSQL how to create a copy of a database or schema?
  28. PostgreSQL: fill NULL values in timeserie query with previous value
  29. Postgres: convert single row to multiple rows (unpivot)
  30. Get most common value for each value of another column in SQL

Leave a Comment